with t1 as(
select distinct
user_id ,
date(start_time) as ride_dt
from hello_bike_riding_rcd
),
t2 as(
select user_id,
ride_dt,
row_number()over(partition by user_id order by ride_dt) as rn
from t1
),
t3 as (
select user_id,
ride_dt,
date_sub(ride_dt,interval rn day) as grp
from t2),
t4 as (
select
user_id,
min(ride_dt) as start_dt,
max(ride_dt) as end_dt,
count(*) as streak
from t3
group by user_id,grp
)
select user_id,
max(streak) as max_streak
from t4
group by user_id
order by max_streak desc
limit 10
WITH RECURSIVE ride_days AS (
SELECT
user_id,
DATE(start_time) AS ride_date,
DATE(end_time) AS end_date
FROM hello_bike_riding_rcd
UNION ALL
SELECT
user_id,
DATE_ADD(ride_date, INTERVAL 1 DAY),
end_date
FROM ride_days
WHERE ride_date < end_date
),
t1 AS (
SELECT DISTINCT
user_id,
ride_date
FROM ride_days
),
t2 AS (
SELECT
user_id,
ride_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY ride_date
) AS rn
FROM t1
),
t3 AS (
SELECT
user_id,
ride_date,
DATE_SUB(ride_date, INTERVAL rn DAY) AS grp
FROM t2
),
t4 AS (
SELECT
user_id,
MIN(ride_date) AS start_date,
MAX(ride_date) AS end_date,
COUNT(*) AS consecutive_days
FROM t3
GROUP BY user_id, grp
)
SELECT
user_id,
MAX(consecutive_days) AS max_streak
FROM t4
GROUP BY user_id
having max_streak>=3
order by max_streak desc
WITH RECURSIVE ride_days AS (
SELECT
user_id,
DATE(start_time) AS ride_date,
DATE(end_time) AS end_date
FROM hello_bike_riding_rcd
UNION ALL
SELECT
user_id,
DATE_ADD(ride_date, INTERVAL 1 DAY),
end_date
FROM ride_days
WHERE ride_date < end_date
),
t1 AS (
SELECT DISTINCT
user_id,
ride_date
FROM ride_days
),
t2 AS (
SELECT
user_id,
ride_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY ride_date
) AS rn
FROM t1
),
t3 AS (
SELECT
user_id,
ride_date,
DATE_SUB(ride_date, INTERVAL rn DAY) AS grp
FROM t2
),
t4 AS (
SELECT
user_id,
MIN(ride_date) AS start_date,
MAX(ride_date) AS end_date,
COUNT(*) AS consecutive_days
FROM t3
GROUP BY user_id, grp
)
SELECT
user_id,
MAX(consecutive_days) AS max_streak
FROM t4
GROUP BY user_id;
with t1 as(
select distinct
employee_id,
date(punch_time) as punch_dt
from attendance
),
t2 as (
select employee_id,
punch_dt,
row_number()over(partition by employee_id order by punch_dt) asrn
from t1
),
t3 as (
select employee_id,punch_dt,
date_sub(punch_dt,interval rn day) as grp
from t2
),
t4 as (
select
employee_id,
min(punch_dt) as start_dt,
max(punch_dt) as end_dt,
count(*) as days
from t3
group by employee_id,grp
)
select
employees.name,max(t4.days) as max_consecutive
from t4 join employees on t4.employee_id = employees.id
group byemployees.name
ORDER BY max_consecutive DESC;
with t1 as(
select distinct
employee_id,
punch_time
from attendance
),
t2 as (
select employee_id,
punch_time,
row_number()over(partition by employee_id order by punch_time) asrn
from t1
),
t3 as (
select employee_id,punch_time,
date_sub(punch_time,interval rn day) as grp
from t2
),
t4 as (
select
employee_id,
min(punch_time) as start_dt,
max(punch_time) as end_dt,
count(*) as days
from t3
group by employee_id,grp
)
select
employees.name,max(t4.days) as max_consecutive
from t4 join employees on t4.employee_id = t4.employee_id
group byemployees.name
WITH t1 AS (
SELECT DISTINCT
usr_id,
DATE(login_time) AS dt
FROM user_login_log
WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
),
t2 AS (
SELECT
usr_id,
dt,
ROW_NUMBER() OVER (PARTITION BY usr_id ORDER BY dt) AS rn
FROM t1
),
t3 AS (
SELECT
usr_id,
dt,
DATE_SUB(dt, INTERVAL rn DAY) AS grp
FROM t2
),
t4 AS (
SELECT
usr_id,
MIN(dt) AS start_dt,
MAX(dt) AS end_dt,
COUNT(*) AS consecutive_days
FROM t3
GROUP BY usr_id, grp
)
SELECT
usr_id,
start_dt AS start_date,
end_dt AS end_date,
consecutive_days
FROM t4
WHERE start_dt <= CURRENT_DATE
AND end_dt >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
AND consecutive_days >= 3
ORDER BY usr_id;
with t1 as(
select
distinct usr_id,
date(login_time) as dt
from user_login_log
),
t2 as (
select
usr_id,
dt,
row_number()over(partition by usr_id order by dt) as rn
from t1
),
t3 as (
select
usr_id,
dt,
date_sub(dt,interval rn day) as grp
from t2),
t4 as (
select usr_id,
min(dt)as start_dt,
max(dt)as end_dt,
count(*) as countnous_days
from t3
group by usr_id ,grp
)
select
usr_id,
start_dt as start_date,
end_dt as end_date,
countnous_days as consecutive_days
from t4
WHERE start_dt <= CURRENT_DATE
AND end_dt >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
and countnous_days >=3
order by usr_id
with t1 as(
select
distinct usr_id,
date(login_time) as dt
from user_login_log
),
t2 as (
select
usr_id,
dt,
row_number()over(partition by usr_id order by dt) as rn
from t1
),
t3 as (
select
usr_id,
dt,
date_sub(dt,interval rn day) as grp
from t2),
t4 as (
select usr_id,
min(dt)as start_dt,
max(dt)as end_dt,
count(*) as countnous_days
from t3
group by usr_id ,grp
)
select
usr_id,
start_dt as start_date,
end_dt as end_date,
countnous_days as consecutive_days
from t4
where end_dt>=date_sub(current_date,interval 30 day)
and countnous_days >=3
order by usr_id
with t1 as(
select
distinct usr_id,
date(login_time) as dt
from user_login_log
),
t2 as (
select
usr_id,
dt,
row_number()over(partition by usr_id order by dt) as rn
from t1
),
t3 as (
select
usr_id,
dt,
date_sub(dt,interval rn day) as grp
from t2),
t4 as (
select usr_id,
min(dt)as start_dt,
max(dt)as end_dt,
count(*) as countnous_days
from t3
group by usr_id ,grp
)
select
usr_id
from t4
where start_dt>=date_sub(current_date,interval 30 day)
and countnous_days >=3
order by usr_id
WITH t1 AS (
SELECT
usr_id,
trx_time,
LAG(trx_time, 3) OVER (
PARTITION BY usr_id
ORDER BY trx_time
) AS prev_3_trx_time
FROM cmb_usr_trx_rcd
WHERE mch_nm = '红玫瑰按摩保健休闲'
)
SELECT DISTINCT
usr_id
FROM t1
WHERE DATEDIFF(trx_time, prev_3_trx_time) <= 3
ORDER BY usr_id;
with t1 as(
select
usr_id,
trx_time
from cmb_usr_trx_rcd
where mch_nm="红玫瑰按摩保健休闲"
)
select distinct
a.usr_id
from t1 a
join t1 b
where a.usr_id=b.usr_id
and b.trx_time >= a.trx_time
and b.trx_time <= date_add(a.trx_time ,interval 3 day)
group by
a.usr_id,a.trx_time
having count(*) >=4
order by a.usr_id asc
with t1 as(
select
usr_id,
trx_time
from cmb_usr_trx_rcd
where mch_nm="红玫瑰按摩保健休闲"
)
select distinct
a.usr_id
from t1 a
join t1 b
where a.usr_id=b.usr_id
and b.trx_time >= a.trx_time
and b.trx_time < date_add(a.trx_time ,interval 3 day)
group by
a.usr_id,a.trx_time
having count(*) >=4
order by a.usr_id asc
with t1 as(
select
usr_id,
trx_time
from cmb_usr_trx_rcd
where mch_nm="红玫瑰按摩保健休闲"
)
select distinct
a.usr_id
from t1 a
join t1 b
where a.usr_id=b.usr_id
and b.trx_time >= a.trx_time
and b.trx_time < date_add(a.trx_time ,interval 3 day)
group by
a.usr_id,a.trx_time
having count(*) >=4
order by a.usr_id
with t1 as(
select
usr_id,
trx_time
from cmb_usr_trx_rcd
where mch_nm="红玫瑰按摩保健休闲"
)
select distinct
a.usr_id
from cmb_usr_trx_rcd a
join cmb_usr_trx_rcd b
where a.usr_id=b.usr_id
and b.trx_time >= a.trx_time
and b.trx_time < date_add(a.trx_time ,interval 3 day)
group by
a.usr_id,a.trx_time
having count(*) >=4
order by a.usr_id
with t1 as(
select
usr_id,
trx_time
from cmb_usr_trx_rcd
where mch_nm="红玫瑰按摩保健休闲"
)
select distinct
a.usr_id
from cmb_usr_trx_rcd a
join cmb_usr_trx_rcd b
where a.usr_id=b.usr_id
and b.trx_time >= a.trx_time
and b.trx_time < date_add(a.trx_time ,interval 3 day)
group by
a.usr_id,a.trx_time
having count(*) >=4
with t1 as (
selectdistinct
usr_id,
date(v_tm) as watch_dt
from bilibili_t20
),
t2 as
(
select usr_id,
watch_dt,
row_number()over(partition by usr_id order by watch_dt) as rn
from t1
) ,
t3 as (
select usr_id,
watch_dt,
date_sub(watch_dt,interval rn day) as grp
from t2
),
t4 as
(select usr_id,
MIN(watch_dt) AS start_dt,
MAX(watch_dt) AS end_dt,
COUNT(*) AS continuous_days
FROM t3
GROUP BY usr_id, grp
ORDER BY usr_id, start_dt
)
SELECT
usr_id,
start_dt,
end_dt,
continuous_days
FROM t4
ORDER BY usr_id, start_dt;
with first as (
select
usr_id,
date_format(login_time,'%Y-%m') as login_month,
count(*) as cishu
from user_login_log
group by usr_id,date_format(login_time,'%Y-%m')
),
second as(
select
usr_id,
login_month
from first
where cishu >=10
)
select
count(distinct usr_id) as inactive_user_count
from
(select
usr_id
from second
group by usr_id
having max(login_month) <='2024-07'
) l