WITH user_jay_songs AS (
SELECT DISTINCT
l.user_id,
DATE(l.start_time) AS listen_date
FROM listen_rcd l
JOIN song_info s ON l.song_id = s.song_id
WHERE s.origin_singer_id in (1,2,3,4,6)
),
date_groups AS (
SELECT
user_id,
listen_date,
DATE_SUB(listen_date, INTERVAL ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY listen_date
) DAY) AS grp
FROM user_jay_songs
),
group_counts AS (
SELECT
user_id,
grp,
COUNT(*) AS consecutive_days
FROM date_groups
GROUP BY user_id, grp
),
max_counts AS (
SELECT
user_id,
MAX(consecutive_days) AS max_consecutive_days
FROM group_counts
GROUP BY user_id
)
SELECT
u.user_id,
COALESCE(m.max_consecutive_days, 0) AS max_consecutive_days
FROM qqmusic_user_info u
LEFT JOIN max_counts m ON u.user_id = m.user_id;
WITH tian_wang_tian_hou_songs AS (
SELECT
si.singer_id,
si.singer_name,
so.song_id
FROM
singer_info si
JOIN
song_info so ON si.singer_id = so.origin_singer_id
WHERE
si.singer_id IN (1, 2, 3, 4,6)
),
listen_tian_wang_songs AS (
SELECT
lr.user_id,
DATE(lr.start_time) AS listen_date
FROM
listen_rcd lr
JOIN
tian_wang_tian_hou_songs twths ON lr.song_id = twths.song_id
),
grouped_dates AS (
SELECT
user_id,
listen_date,
DATE_SUB(listen_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY listen_date) DAY) AS grp
FROM
listen_tian_wang_songs
),
consecutive_days AS (
SELECT
user_id,
grp,
COUNT(*) AS consecutive_days
FROM
grouped_dates
GROUP BY
user_id,
grp
)
SELECT
user_id,
MAX(consecutive_days) AS max_consecutive_days
FROM
consecutive_days
GROUP BY
user_id;
WITH tem AS
(
select
sii.singer_id
,sii.singer_name
,ali.album_id
,ali.album_name
,soi.song_id
from
song_info soi
LEFT JOIN
album_info ali
ON ali.album_id = soi.album_id
left join
singer_info sii
ON sii.singer_id = ali.singer_id
where
sii.singer_id IN (1,2,3,4,6)
),
tem2 AS
(select
qui.user_id
,date_format(lir.start_time - INTERVAL row_number() OVER (PARTITION BY qui.user_id ORDER BY lir.start_time) DAY, '%Y-%m-%d') AS grp_day
from
qqmusic_user_info qui
LEFT JOIN
listen_rcd lir
ON lir.user_id = qui.user_id
where
song_id IN
(select
song_id
from
tem
)
),
tem3 AS(select
user_id
,grp_day
,COUNT(*) AS consecutive_days
from
tem2
GROUP BY
user_id
,grp_day)
select
user_id
,MAX(consecutive_days) AS max_consecutive_days
from tem3
GROUP BY user_id
WITH tian_wang_tian_hou_songs AS (
SELECT
si.singer_id,
si.singer_name,
si.type1,
si.type2,
si.type3,
so.song_id
FROM
singer_info si
JOIN
song_info so ON si.singer_id = so.origin_singer_id
WHERE
si.singer_id IN (1, 2, 3, 4)
),
listen_tian_wang_songs AS (
SELECT
lr.user_id,
DATE(lr.start_time) AS listen_date
FROM
listen_rcd lr
JOIN
tian_wang_tian_hou_songs twths ON lr.song_id = twths.song_id
),
grouped_dates AS (
SELECT
user_id,
listen_date,
DATE_SUB(listen_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY listen_date) DAY) AS grp
FROM
listen_tian_wang_songs
),
consecutive_days AS (
SELECT
user_id,
grp,
COUNT(*) AS consecutive_days
FROM
grouped_dates
GROUP BY
user_id,
grp
)
SELECT
user_id,
MAX(consecutive_days) AS max_consecutive_days
FROM
consecutive_days
GROUP BY
user_id;
WITH tem AS
(
select
sii.singer_id
,sii.singer_name
,ali.album_id
,ali.album_name
,soi.song_id
from
song_info soi
LEFT JOIN
album_info ali
ON ali.album_id = soi.album_id
left join
singer_info sii
ON sii.singer_id = ali.singer_id
where
sii.singer_id IN (1,2)
),
tem2 AS
(select
qui.user_id
,date_format(lir.start_time - INTERVAL row_number() OVER (PARTITION BY qui.user_id ORDER BY lir.start_time) DAY, '%Y-%m-%d') AS grp_day
from
qqmusic_user_info qui
LEFT JOIN
listen_rcd lir
ON lir.user_id = qui.user_id
where
song_id IN
(select
song_id
from
tem
)
),
tem3 AS(select
user_id
,grp_day
,COUNT(*) AS consecutive_days
from
tem2
GROUP BY
user_id
,grp_day)
select
user_id
,MAX(consecutive_days) AS max_consecutive_days
from tem3
GROUP BY user_id