with a1 as(
select
distinct usr_id,
date(login_time) as login_time
from
user_login_log
where
datediff(current_date,date(login_time)) <=30
),
a2 as(
select
a.usr_id,
a.login_time as T_date,
b.login_time as T1_date
from
a1 as a
left join
a1 as b
on
a.usr_id = b.usr_id
and
datediff(a.login_time,b.login_time) = -1
)
select
T_date,
concat(round(avg(T1_date is not null)*100,2),"%") as T1_retention_rate
from
a2
group by
T_date
order by
T_date
with a1 as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
login_time >= date_sub(current_date,interval 30 day)
group by
usr_id,
DATE(login_time)
),
a2 as(
select
a.usr_id,
a.login_date AS first_login_date,
b.login_date AS next_day_login_date,
datediff(b.login_date,a.login_date) as days_diff
from
a1 as a
left join
a1 as b
on
a.usr_id = b.usr_id
and
b.login_date between a.login_date + interval 1 day and a.login_date + interval 14 day
)
select
first_login_date,
round(count(distinct case when days_diff = 1 then usr_id end)*100/count(distinct usr_id),2) AS t_plus_1_retention_rate,
round(count(distinct case when days_diff = 3 then usr_id end)*100/count(distinct usr_id),2) AS t_plus_3_retention_rate,
round(count(distinct case when days_diff = 7 then usr_id end)*100/count(distinct usr_id),2) AS t_plus_7_retention_rate,
round(count(distinct case when days_diff = 14 then usr_id end)*100/count(distinct usr_id),2) AS t_plus_14_retention_rate
from
a2
where
first_login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
group by
first_login_date
order by
first_login_date
with a1 as(
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date,date(login_time)) <= 30
),
a2 as(
select
a.usr_id,
a.login_date as T_date,
b.login_date as T1_date
from
a1 as a
left join
a1 as b
on
a.usr_id = b.usr_id
and
datediff(a.login_date,b.login_date) = -1
)
select
T_date as first_login_date,
concat(round(avg(T1_date is not null)*100,2),'%') as T1_retention_rate
from
a2
group by
T_date
order by
T_date
select *
from hand_permutations
where
concat(card1, card2) like '%A%A%' or
concat(card1, card2) like '%A%K%' or
concat(card1, card2) like '%K%K%' or
concat(card1, card2) like '%K%A%'
order by id;
with a1 as(
select
ts_code,
symbol,
name,
area,
industry,
list_date,
row_number()over(partition by industry order by list_date) as list_dateing
from
stock_info
where
name LIKE '中国%' OR name LIKE '%中%'
)
select
ts_code,
symbol,
name,
area,
industry,
list_date
from
a1
where
list_dateing = 1
order by
industry
with a1 as(
select
ts_code,
symbol,
name,
area,
industry,
list_date,
row_number()over(partition by industry order by list_date) as list_dateing
from
stock_info
where
name LIKE '中国%' OR name LIKE '%中%'
)
select
ts_code,
symbol,
name
area,
industry,
list_date
from
a1
where
list_dateing = 1
order by
industry
with a1 as(
select
ts_code,
symbol,
name,
area,
industry,
list_date,
row_number()over(partition by industry order by list_date) as list_dateing
from
stock_info
where
name LIKE '中国%' OR name LIKE '%中%'
)
select
ts_code,
symbol,
name
area,
industry
from
a1
where
list_dateing = 1
order by
industry
with a1 as(
select
ts_code,
symbol,
name,
area,
industry,
list_date,
row_number()over(partition by industry order by list_date) as list_dateing
from
stock_info
)
select
ts_code,
symbol,
name like '中国%' or name like '%中%' as name,
area,
industry
from
a1
where
list_dateing = 1
with a1 as (
select
user_id,
product_type,
row_number() over (partition by user_id order by purchase_time ASC) as mt
from
apple_pchs_rcd
),
a2 as(
select
user_id,
MAX(CASE WHEN mt = 1 THEN product_type END) AS first_product,
MAX(CASE WHEN mt = 2 THEN product_type END) AS second_product
from
a1
group by
user_id
)
select
user_id,
case
when first_product = 'Mac' and second_product = 'iPhone' then 1 else 0 end as tag
from
a2
group by
user_id
with a1 as (
select
user_id,
product_type,
row_number() over (partition by user_id order by purchase_time ASC) as mt
from
apple_pchs_rcd
)
select
user_id,
case when product_type = 'Mac' then '1' else '0' end as tag
from
a1
where
mt = 1
order by
user_id;
with a1 as
(select
a.user_id,
a.mt,
product_type
from(
select
user_id,
min(purchase_time) as mt
from
apple_pchs_rcd
group by
user_id
)a
join
apple_pchs_rcd b
on
a.user_id = b.user_id
)
select
user_id,
case when product_type = 'Mac' then '1' else '0' end as tag
from
a1
order by
1
select
count(distinct case
when time(login_time) between'07:30:00' and '09:30:00'
or time(login_time) between'18:30:00' and '20:30:00' then usr_id
END) AS commute,
COUNT(DISTINCT CASE
WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN usr_id
END) AS lunch_break,
COUNT(DISTINCT CASE
WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' THEN usr_id
WHEN TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' THEN usr_id
END) AS bedtime
from
user_login_log
where
login_time >= date_format(date_sub(curdate(),interval 1 month),'%Y-%m-01')
and
login_time < date_format(current_date(),'%Y-%m-01')
with a1 as(
select
year,
avg_tmp_h,
LAG(avg_tmp_h,1,1000) OVER (ORDER BY year) AS last_year_avg
from(
select
year(dt) as year,
cast(avg(tmp_h) as decimal(4,2)) as avg_tmp_h
from
weather_rcd_china
where
year(dt) between 2011 and 2022
and
city = 'shenzhen'
group by
year(dt)
)a
)
select
a1.year,
a1.avg_tmp_h,
case when abs(avg_tmp_h - COALESCE(last_year_avg,0))between 1 and 100 THEN 'Yes'
ELSE 'No' end as significant_change
from
a1
order by
1
with a1 as(
select
year,
avg_tmp_h,
LAG(avg_tmp_h) OVER (ORDER BY year) AS last_year_avg
from(
select
year(dt) as year,
cast(avg(tmp_h) as decimal(4,2)) as avg_tmp_h
from
weather_rcd_china
where
year(dt) between 2011 and 2022
and
city = 'shenzhen'
group by
year(dt)
)a
)
select
a1.year,
a1.avg_tmp_h,
case when abs(avg_tmp_h - last_year_avg) <1 then 'NO' else 'YES' end as significant_change
from
a1
order by
1
select
year(dt),
city,
round(avg(tmp_h),2)
from
weather_rcd_china
where
year(dt) between 2011 and 2022
and
city in ('beijing','shanghai','guangzhou','shenzhen')
group by
year(dt),city
order by
1
select
city,
count(case when con like '%雪%' then 1 else null end) as snowy_days
from
weather_rcd_china
where
month(dt) in (12,1,2)
group by
city
order by
2 desc
select
count(distinct usr_id) as inactive_user_count
from
user_login_log
where
date(login_time) <= '2024-10-01'
and not exists (
select 1
from user_login_log as log2
where log2.usr_id = user_login_log.usr_id
and date(log2.login_time) > '2024-10-01'
);
select
a.trx_mon,
sum(monthly_trx_amt) over (order by trx_mon) as trx_amt
from
(
select
substr(a.trx_time,1,7) as trx_mon,
sum(a.trx_amt) as monthly_trx_amt
from
cmb_usr_trx_rcd a
join
cmb_mch_typ b
on
a.mch_nm = b.mch_nm
where
a.usr_id = '5201314520'
and
mch_typ = '休闲娱乐'
and
year(a.trx_time) in (2023,2024)
group by
substr(trx_time,1,7)
order by
1 asc
)a