SELECT
sum(TIMESTAMPDIFF(SECOND, call_time, grab_time))/count(1) AS avg_response_time_seconds
FROM
didi_order_rcd
WHERE
grab_time != '1970-01-01 00:00:00';
SELECT
LPAD(HOUR(enter_time), 2, '0') AS hour_entered,
COUNT(1) AS enter_count
FROM ks_live_t1 t1
JOIN ks_live_t2 t2 ON t1.live_id = t2.live_id
WHERE enter_time IS NOT NULL
GROUP BY LPAD(HOUR(enter_time), 2, '0')
ORDER BY hour_entered ASC;
SELECT usr_id, mch_nm ,trx_time, trx_amt
FROM (
SELECT
*,
RANK() OVER (ORDER BY trx_amt DESC) AS amt_rank
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
AND YEAR(trx_time) = 2024
) t
WHERE t.amt_rank = 1;
SELECT *
FROM (
SELECT
*,
RANK() OVER (ORDER BY trx_amt DESC) AS amt_rank
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
AND YEAR(trx_time) = 2024
) t
WHERE t.amt_rank = 1;
select usr_id,mch_nm,sum(trx_amt) as trx_amt,count(*)as trx_cnt,min(trx_time)as first_time
from cmb_usr_trx_rcd
where usr_id='5201314520'
and trx_amt>=288
group by usr_id,mch_nm
order by trx_cnt desc
select trx_amt,count(*)as total_trx_cnt,
count(distinct(usr_id)) as unique_usr_cnt,
count(*)/count(distinct(usr_id)) as avg_trx_per_user
from cmb_usr_trx_rcd
where
mch_nm = '红玫瑰按摩保健休闲'
and
(year(trx_time) = 2023
or (year(trx_time) = 2024 and month(trx_time) between 1 and 6)
)
group by
trx_amt
order by
avg_trx_per_user desc
limit 5;
select trx_amt,count(*)as total_trx_cnt,
count(distinct(usr_id)) as unique_usr_cnt,
count(*)/count(distinct(usr_id)) as avg_trx_per_user
from cmb_usr_trx_rcd
where
mch_nm = '红玫瑰按摩保健休闲'
and
(
(year(trx_time) = 2023 and month(trx_time) between 1 and 12)
or (year(trx_time) = 2024 and month(trx_time) between 1 and 6)
)
group by
trx_amt
order by
avg_trx_per_user desc
limit 5;
select trx_amt,count(*) as trx_cnt
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and year(trx_time) = 2024
and month(trx_time) in (1, 2, 3, 4, 5, 6, 7)
group by trx_amt
order by trx_cnt desc
limit 5
select trx_amt,count(*) as trx_cnt
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and year(trx_time) = 2024
and month(trx_time) in (1, 2, 3, 4, 5, 6, 7)
group by trx_amt
order by trx_cnt desc
select date(trx_time) as trx_date,
max(trx_amt)as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲' and date(trx_time)between '2024-09-01' and '2024-09-30'
group by date(trx_time)
order by trx_date
select date(trx_time) as trx_date,
max(trx_time)as max_trx_amt,
min(trx_time) as min_trx_amt,
avg(trx_time) as avg_trx_amt,
sum(trx_time) as total_trx_amt
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲' and date(trx_time)between '2024-09-01' and '2024-09-30'
group by date(trx_time)
order by trx_date
select date(trx_time) as trx_date,
max(trx_time)as max_trx_amt,
min(trx_time) as min_trx_amt,
avg(trx_time) as avg_trx_amt,
sum(trx_time) as total_trx_amt
from cmb_usr_trx_rcd
where date(trx_time)between '2024-09-01' and '2024-09-30'
and mch_nm='红玫瑰按摩保健休闲'
group by date(trx_time)
order by trx_date
select mch_nm ,sum(trx_amt) as sum_trx_amt from cmb_usr_trx_rcd
where year(trx_time)='2024' and usr_id='5201314520'
group by mch_nm
order by sum_trx_amt desc