select 
	tb_prd_map.prd_id,
tb_prd_map.prd_nm,
sum(if_snd) as exposure_count
from 
	tb_pg_act_rcd
join
	tb_prd_map on tb_pg_act_rcd.prd_id=tb_prd_map.prd_id
group by
	tb_prd_map.prd_id,tb_prd_map.prd_nm
order by
	exposure_count desc
limit 1;

select 
	cust_uid,
count(distinct car_cls) as unique_car_classes
from didi_sht_rcd
group by 
	cust_uid
order by
	unique_car_classes desc;

select 
	r.* 
from didi_sht_rcd as r
join
	loc_nm_ctg l 
on r.end_loc=l.loc_nm
where
	l.loc_ctg='餐饮'
order by
	r.start_tm asc

select 
	car_cls,
count(car_cls) as trip_count
from didi_sht_rcd 
group by
	car_cls
order by
	trip_count desc;

select 
	*
from didi_sht_rcd
where
start_loc='海底捞西丽店' or end_loc='海底捞西丽店'

with user_login_days as(
	select usr_id,
			date(login_time) as login_date
	from user_login_log
	where
		login_time >=date_sub(curdate(),interval 180 day)),
distinct_login_days as (
	select
		usr_id,
		count(distinct login_date) as login_days
	from
		user_login_days
	group by
		usr_id)
select
	sum(
case when
	login_days between 1 and 5 
then 1 
else 0 
end) as days_1_to_5,
	sum(case when
 	login_days between 6 and 10
 then 1
 else 0
 end) as days_6_to_10,
	sum(case when
 		login_days between 11 and 20
 then 1
 else 0
 end) as days_11_to_20,
	sum(case when
 		login_days >20 
 then 1
 else 0 
 end) as days_over_20
from distinct_login_days;

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(CURDATE(), '%Y-%m-01');

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 '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(curdate(),'%Y-%m-01')

select 
	count(distinct usr_id) as active_users
from 
	user_login_log
where 
	login_time >= date_format(date_sub(curdate(),interval 1 month),'%Y-%m-01 00:00:00') and
login_time < date_format(curdate(),'%Y-%m-01 00:00:00')
;

select 
	count(distinct usr_id) as active_users
from 
	user_login_log
where 
	date(login_time) between '2024-10-01' and '2024-10-31'
;

select 
	count(date(login_time) between '2024-10-01' and '2024-10-31') as active_users
from user_login_log;

select 
	ts_code,
open_price,
close_price
from 
	daily_stock_prices
where
	trade_date='2023-10-9'
and pct_change > 1
limit 5;

select 
	year(list_date) as Y,
count(distinct ts_code) as cnt 
from 
	stock_info
where
	ts_code like '%SZ'
and industry= '银行'
group by
	Y
order by
	Y asc;

select 
	year(dt) as Y,
cast(
avg(case when
	city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京',
cast(
avg(case when
	city='shanghai' then tmp_h else null end) as decimal(4,2)) as '上海',
cast(
avg(case when
	city='shenzhen' then tmp_h else null end) as decimal(4,2)) as '深圳',
cast(
avg(case when
	city='guangzhou' then tmp_h else null end) as decimal(4,2)) as '广州' 
from 
	weather_rcd_china
where
	year(dt) between 2011 and 2022
group by
	year(dt);

select 
	city,
sum(case when con like '%雪%' then 1 else 0 end)as snowy_days
from 
	weather_rcd_china 
where
	month(dt) in (12,1,2)
group by
	city
order by
	snowy_days desc;

select 
	city,
count(*) as cloudy_days,
concat(cast(count(*)/365*100 as decimal(4,2)),'%') as p
from 
	weather_rcd_china
where
	con like '%多云%'
 	and year(dt)=2021
group by
	city
order by
	cloudy_days desc;

select 
	city,
	cast(avg(tmp_h)as decimal(4,2)) as avg_tmp_h
from 
	weather_rcd_china 
where
	year(dt)=2021
group by
	city
order by
	avg_tmp_h desc

select 
	city,
	round(avg(tmp_h),2) as avg_tmp_h
from 
	weather_rcd_china 
group by
	city
order by
	avg_tmp_h desc

select 
	* 
from 
	didi_order_rcd 
where
	cust_uid='kjhd30'
and date(finish_time)='1970-01-01'
order by
	call_time asc
limit 1;

select 
	avg(timestampdiff(second,grab_time,call_time))as avg_response_time_seconds
from 
	didi_order_rcd
where
	date(finish_time) !='1970-01-01'
and date(grab_time) !='1970-01-01'
;