with tmp1 as (
select 
user_id,
product_type,
row_number() over(partition by user_id order by purchase_time) as rk
from apple_pchs_rcd
)
select 
user_id,
case 
when sum(rk=1 and product_type='Mac')=1 and sum(rk=2 and product_type='iPhone')=1
then 1
else 0
end as tag
from tmp1
group by user_id

with tmp1 as (
select 
snd_usr_id,
rcv_usr_id,
snd_datetime,
lag(snd_datetime,2) over (partition by snd_usr_id order by snd_datetime) as last_2_datetime,
pkt_amt,
t3.cty as recipient_city,
row_number() over(partition by snd_usr_id order by snd_datetime) as rk
from tx_red_pkt_rcd t1 join tx_usr_bas_info t2 on t1.snd_usr_id=t2.usr_id join tx_usr_bas_info t3 on t1.rcv_usr_id=t3.usr_id
where date(snd_datetime)='2021-02-13' and t2.gdr='M' and t3.gdr='F' 
),
tmp2 as (
select 
snd_usr_id,
rk
from tmp1
where timestampdiff(second,last_2_datetime,snd_datetime)<=3600
)
select distinct
tmp1.snd_usr_id,
rcv_usr_id,
snd_datetime,
pkt_amt,
recipient_city
from tmp1 join tmp2 on tmp1.rk between cast(tmp2.rk as signed)-2 and tmp2.rk and tmp1.snd_usr_id=tmp2.snd_usr_id
order by tmp1.snd_usr_id,snd_datetime

select 
avg(pkt_amt) as median
from (
select 
pkt_amt,
row_number() over(order by pkt_amt) as rk,
count(*) over() as cnt
from tx_red_pkt_rcd 
) as tmp
where rk-cnt/2 between 0 and 1

select least(snd_usr_id, rcv_usr_id) as user_a,
 greatest(snd_usr_id, rcv_usr_id) as user_b
from tx_red_pkt_rcd
where date(snd_datetime) = '2021-02-13'
group by user_a, user_b
having count(distinct snd_usr_id, rcv_usr_id) >= 2

select count(*) as cnt
from (
select usr_id as only_cinema_users
from bilibili_t20 t20 join bilibili_t3 t3 using (v_id)
group by usr_id
having count(case when v_typ<>'放映厅' then 1 end)=0
) as tmp

WITH FirstLogin AS (
    SELECT
        usr_id,
        MIN(v_date) AS first_login_date
    FROM
        bilibili_t100
    GROUP BY
        usr_id
),
NewUsers AS (
    SELECT
        first_login_date,
        COUNT(*) AS new_users
    FROM
        FirstLogin
    GROUP BY
        first_login_date
),
NewMembers AS (
    SELECT
        t0.v_date AS first_membership_date,
        COUNT(*) AS new_members
    FROM
        bilibili_t0 t0
    JOIN
        FirstLogin fl ON t0.usr_id = fl.usr_id
    WHERE
        t0.m_flg = 1 AND t0.v_date = fl.first_login_date
    GROUP BY
        t0.v_date
)
SELECT
    nu.first_login_date AS login_date,
    nu.new_users,
    nm.new_members,
    ROUND(nm.new_members * 100.0 / nu.new_users, 2) AS conversion_rate
FROM
    NewUsers nu
LEFT JOIN
    NewMembers nm ON nu.first_login_date = nm.first_membership_date
ORDER BY
    nu.first_login_date;

with tmp1 as (
select distinct
usr_id,
first_value(v_date) over w as login_date,
first_value(m_flg) over w as m_flg
from bilibili_t100
window w as (partition by usr_id order by v_date)
)
select 
login_date,
count(*) as new_users,
sum(m_flg) as new_members,
round(100*sum(m_flg)/count(*),2) as conversion_rate
from tmp1
group by login_date

with recursive tmp1 as (
select 
1 as week_number
union all 
select week_number+1
from tmp1
where week_number<(select floor(dayofyear('2021-12-31')/7)+1)
),
tmp2 as (
select 
user_id,
case when begin_date<'2021-01-01' then '2021-01-01'
else begin_date
end as b_dt,
case when end_date>'2021-12-31' then '2021-12-31'
else end_date
end as e_dt,
pay_amount/(datediff(end_date,begin_date)+1) as avg_day_amount
from bilibili_m1
where begin_date<='2021-12-31' and end_date>='2021-01-01' and begin_date<=end_date
) ,
tmp3 as (
select 
user_id,
floor(dayofyear(b_dt)/7)+1 as week_num_begin,
dayofyear(b_dt)%7 as begin_day,
floor(dayofyear(e_dt)/7)+1 as week_num_end,
dayofyear(e_dt)%7 as end_day,
avg_day_amount
from tmp2
),
tmp4 as (
select 
week_number,
user_id,
case 
when week_num_begin=week_num_end
then end_day-begin_day+1 
when week_number=week_num_begin
then 7-begin_day+1
when week_number=week_num_end 
then end_day
else 7
end as days,
avg_day_amount
from tmp1 join tmp3 on tmp1.week_number between tmp3.week_num_begin and tmp3.week_num_end
)
select week_number,round(sum(days*avg_day_amount),2) as total_weekly_income
from tmp4
group by week_number
order by week_number

with tmp1 as (
select 
user_id,
case when begin_date<='2022-01-31' then '2022-01-31'
else begin_date
end as b_dt,
case when end_date>='2022-02-06' then '2022-02-06'
else end_date
end as e_dt,
pay_amount/(datediff(end_date,begin_date)+1) as avg_day_amount
from bilibili_m1 
where end_date>='2022-01-31' and begin_date<='2022-02-06' and begin_date<=end_date
)
select round(sum((datediff(e_dt,b_dt)+1)*avg_day_amount),2) as total_holiday_income
from tmp1

select 
u1.user_id,
sum(u1.steps<coalesce(u2.steps,0))+1 as row_num
fromuser_steps u1 left join friend_relationships f1 on f1.user1=u1.user_id left join user_steps u2 on f1.user2=u2.user_id
group by u1.user_id
order by u1.user_id

select 
u1.user_id,
sum(u1.steps<u2.steps)+1 as row_num
from friend_relationships f1 join user_steps u1 on f1.user1=u1.user_id join user_steps u2 on f1.user2=u2.user_id
group by u1.user_id
order by u1.user_id

SELECT 
CASE 
WHEN HOUR(v_tm) BETWEEN 6 AND 12 THEN '上午'
WHEN HOUR(v_tm) BETWEEN 12 AND 18 THEN '下午'
WHEN HOUR(v_tm) BETWEEN 18 AND 22 THEN '晚间'
ELSE '深夜'
END AS time_period,
COUNT(*) AS view_count,
COUNT(DISTINCT usr_id) AS active_users
FROM bilibili_t20
GROUP BY time_period
ORDER BY 
CASE 
WHEN time_period = '上午' THEN 1
WHEN time_period = '下午' THEN 2
WHEN time_period = '晚间' THEN 3
ELSE 4
END

WITH daily_punch AS (
SELECT 
employee_id,
DATE(punch_time) AS punch_date,
MIN(TIME(punch_time)) AS first_punch,
MAX(TIME(punch_time)) AS last_punch
FROM attendance
GROUP BY employee_id, DATE(punch_time)
)
SELECT 
employee_id,
SUM(CASE WHEN first_punch > '09:00:00' THEN 1 ELSE 0 END) AS late_count,
SUM(CASE WHEN last_punch < '18:00:00' THEN 1 ELSE 0 END) AS early_leave_count,
SUM(CASE WHEN first_punch > '09:00:00' OR last_punch < '18:00:00' THEN 1 ELSE 0 END) AS abnormal_count
FROM daily_punch
GROUP BY employee_id
ORDER BY abnormal_count DESC
LIMIT 3

SELECT 
e.name,
a.timestamp,
CASE 
WHEN HOUR(a.timestamp) < 6 THEN '凌晨异常'
WHEN HOUR(a.timestamp) > 22 THEN '深夜异常'
ELSE '正常时段'
END AS time_type,
a.access_type
FROM employees e
JOIN access_control a ON e.id = a.employee_id
WHERE HOUR(a.timestamp) < 6 OR HOUR(a.timestamp) > 22
ORDER BY a.timestamp DESC
LIMIT 3

WITH daily_hours AS (
SELECT 
employee_id,
DATE(punch_time) AS work_date,
TIMESTAMPDIFF(HOUR, MIN(punch_time), MAX(punch_time)) AS work_hours
FROM attendance
GROUP BY employee_id, DATE(punch_time)
)
SELECT 
employee_id,
COUNT(*) AS work_days,
SUM(CASE WHEN work_hours > 9 THEN work_hours - 9 ELSE 0 END) AS overtime_hours
FROM daily_hours
GROUP BY employee_id
ORDER BY overtime_hours DESC
LIMIT 3

SELECT 
HOUR(start_time) AS hour,
COUNT(*) AS ride_count,
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS hour_rank
FROM hello_bike_riding_rcd
GROUP BY HOUR(start_time)
ORDER BY hour
LIMIT 5

SELECT 
user_id,
CASE 
WHEN HOUR(start_time) BETWEEN 0 AND 5 THEN '0-6点'
WHEN HOUR(start_time) BETWEEN 6 AND 11 THEN '6-12点'
WHEN HOUR(start_time) BETWEEN 12 AND 17 THEN '12-18点'
ELSE '18-24点'
END as time_range,
COUNT(*) as listen_cnt
FROM listen_rcd
GROUP BY user_id, time_range
ORDER BY user_id, listen_cnt DESC;

SELECT 
e.name,
e.department,
f.daily_interaction,
f.daily_messages,
f.daily_documents,
DENSE_RANK() OVER (ORDER BY f.daily_interaction DESC) AS activity_rank
FROM employees e
JOIN flybook_stats f ON e.id = f.employee_id
ORDER BY activity_rank
LIMIT 5

SELECT 
t20.usr_id,
COUNT(DISTINCT t3.v_typ) AS type_count,
COUNT(*) AS total_views,
ROUND(COUNT(DISTINCT t3.v_typ) * 100.0 / COUNT(*), 2) AS diversity_index,
DENSE_RANK() OVER (ORDER BY COUNT(DISTINCT t3.v_typ) DESC) AS diversity_rank
FROM bilibili_t20 t20
JOIN bilibili_t3 t3 ON t20.v_id = t3.v_id
GROUP BY t20.usr_id
ORDER BY diversity_rank
LIMIT 3

WITH user_stats AS (
SELECT 
t20.usr_id,
COUNT(*) AS view_count,
COUNT(DISTINCT DATE(t20.v_tm)) AS active_days
FROM bilibili_t20 t20
GROUP BY t20.usr_id
)
SELECT 
CASE WHEN t100.m_flg = 1 THEN '会员' ELSE '非会员' END AS member_type,
COUNT(DISTINCT u.usr_id) AS user_count,
ROUND(AVG(u.view_count), 2) AS avg_views,
ROUND(AVG(u.active_days), 2) AS avg_active_days
FROM user_stats u
JOIN bilibili_t100 t100 ON u.usr_id = t100.usr_id
GROUP BY t100.m_flg