WITH total_calculation AS
(SELECT k1.live_id, k2.live_nm, k2.live_type,
SUM(TIMESTAMPDIFF(SECOND, k1.enter_time, k1.leave_time)) AS total_duration,
COUNT(DISTINCT usr_id) AS total_users,
SUM(TIMESTAMPDIFF(SECOND, k1.enter_time, k1.leave_time))/ NULLIF(COUNT(DISTINCT usr_id),0) AS avg_duration
FROM ks_live_t1 AS k1
INNER JOIN ks_live_t2 AS k2 ON k1.live_id = k2.live_id
WHERE k1.enter_time >= '2021-09-12 23:00:00' ANDk1.leave_time <='2021-09-13 03:59:59'
GROUP BY k1.live_id, k2.live_nm, k2.live_type)
SELECT
live_id, live_nm, live_type, total_duration, total_users, avg_duration
FROM (
SELECT
*, ROW_NUMBER()OVER(PARTITION BY live_type ORDER BY avg_duration DESC) AS rn
FROM total_calculation
)AS subquery
WHERE rn = 1
ORDER BY live_id
WITH num_enter AS(
SELECT
k1.live_id,
k2.live_nm,
k2.live_type,
COUNT(k1.enter_time)AS enter_cnt
FROM ks_live_t1 AS k1
INNER JOIN ks_live_t2 AS k2
ON k1.live_id = k2.live_id
WHERE DATE(k1.enter_time) = '2021-09-12' AND LPAD(HOUR(enter_time),2,'0') = '23'
GROUP BY k1.live_id,k2.live_nm,k2.live_type
)
SELECT live_id, live_nm, live_type, enter_cnt
FROM(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY live_type ORDER BY enter_cnt DESC) AS rn
FROM num_enter) AS ranked
WHERE rn = 1
ORDER BY live_id
SELECT
k1.live_id,
k2.live_nm,
COUNT(k1.enter_time) AS enter_cnt
FROM ks_live_t1 AS k1
INNER JOIN ks_live_t2 AS k2
ON k1.live_id = k2.live_id
WHERE DATE (k1.enter_time) = '2021-09-12' AND LPAD(HOUR(enter_time),2,'0') = 23
GROUP BY k1.live_id, k2.live_nm
ORDER BY enter_cnt DESC
LIMIT 5
SELECT
LPAD(HOUR(enter_time),2,'0') AS hour_entered,
COUNT(*) AS enter_count
FROM ks_live_t1
GROUP BY LPAD(HOUR(enter_time),2,'0')
ORDER BY hour_entered
WITH all_orders AS(
SELECT
COUNT(DISTINCT order_id) AS total_orders
FROM didi_order_rcd
WHERE call_time != '1970-01-01 00:00:00' AND DATE(call_time) = '2021-05-03'
),
grab_orders AS(
SELECT
COUNT(DISTINCT order_id) AS answered_orders
FROM didi_order_rcd
WHERE grab_time != '1970-01-01 00:00:00' AND DATE(call_time) = '2021-05-03'
)
SELECT
ao.total_orders,
go.answered_orders,
CONCAT(ROUND(100*go.answered_orders/ao.total_orders,2),'%') AS answer_rate
FROM all_orders AS ao, grab_orders AS go
WITH all_orders AS(
SELECT
HOUR(start_tm) AS hour,
COUNT(*) AS total_orders
FROM didi_sht_rcd
GROUP BY HOUR(start_tm)
),
A_class_orders AS(
SELECT
HOUR(start_tm) AS hour,
car_cls,
COUNT(*) AS A_orders
FROM didi_sht_rcd
WHERE car_cls = 'A'
GROUP BY HOUR(start_tm), car_cls
)
SELECT
allor.hour,
acla.A_orders,
allor.total_orders,
ROUND(acla.A_orders/allor.total_orders*100,2) AS percentage_A_orders
FROM all_orders AS allor
INNER JOIN A_class_orders AS acla
ON allor.hour = acla.hour
WHERE acla.hour BETWEEN 18 AND 23
ORDER BY acla.hour ASC
WITH trips AS(
SELECT
start_loc,
end_loc,
ctg1.loc_ctg AS start_ctg,
ctg2.loc_ctg AS end_ctg,
COUNT(*) AS trip_count
FROM
didi_sht_rcd AS rcd
INNER JOIN
loc_nm_ctg AS ctg1 ON rcd.start_loc = ctg1.loc_nm
INNER JOIN
loc_nm_ctg AS ctg2 ON rcd.end_loc = ctg2.loc_nm
GROUP BY
start_loc, end_loc, ctg1.loc_ctg, ctg2.loc_ctg
),
top_trip AS(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY start_ctg, end_ctg ORDER BY trip_count DESC) AS rk
FROM
trips
)
SELECT
start_loc, end_loc, start_ctg, end_ctg, trip_count
FROM
top_trip
WHERE
(start_ctg = "酒店" OR end_ctg = "酒店") AND rk = 1
ORDER BY
trip_count DESC
WITH trips AS(
SELECT
start_loc,
end_loc,
ctg1.loc_ctg AS start_ctg,
ctg2.loc_ctg AS end_ctg,
COUNT(*) AS trip_count
FROM
didi_sht_rcd AS rcd
INNER JOIN
loc_nm_ctg AS ctg1 ON rcd.start_loc = ctg1.loc_nm
INNER JOIN
loc_nm_ctg AS ctg2 ON rcd.end_loc = ctg2.loc_nm
GROUP BY
start_loc, end_loc, ctg1.loc_ctg, ctg2.loc_ctg
),
top_trip AS(
SELECT
*,
RANK() OVER(PARTITION BY start_ctg, end_ctg ORDER BY trip_count DESC) AS rk
FROM
trips
)
SELECT
start_loc, end_loc, start_ctg, end_ctg, trip_count
FROM
top_trip
WHERE
(start_ctg = "酒店" OR end_ctg = "酒店") AND rk = 1
ORDER BY
trip_count DESC
WITH trip_count AS (
SELECT
rcd.start_loc,
rcd.end_loc,
ctg2.loc_ctg,
COUNT(*) AS trip_count
FROM
didi_sht_rcd AS rcd
INNER JOIN
loc_nm_ctg AS ctg1 ON rcd.start_loc = ctg1.loc_nm
INNER JOIN
loc_nm_ctg AS ctg2 ON rcd.end_loc = ctg2.loc_nm
WHERE
ctg1.loc_ctg = "酒店"
GROUP BY
rcd.start_loc, rcd.end_loc, ctg2.loc_ctg),
top_trips AS(
SELECT
*,
RANK() OVER (PARTITION BY loc_ctg ORDER BY trip_count DESC) AS rk
FROM
trip_count
)
SELECT
start_loc, end_loc, loc_ctg, trip_count
FROM
top_trips
WHERE
rk = 1
ORDER BY
trip_count DESC
SELECT
rcd.start_loc,
rcd.end_loc,
ctg2.loc_ctg,
COUNT(*) AS trip_count
FROM
didi_sht_rcd AS rcd
INNER JOIN
loc_nm_ctg AS ctg1 ON rcd.start_loc = ctg1.loc_nm
INNER JOIN
loc_nm_ctg AS ctg2 ON rcd.end_loc = ctg2.loc_nm
WHERE
ctg1.loc_ctg = "酒店"
GROUP BY
rcd.start_loc, rcd.end_loc, ctg2.loc_ctg
ORDER BY
trip_count DESC
SELECT
start_loc,
end_loc,
COUNT(*) AS trip_count
FROM
(SELECT
start_loc,
end_loc
FROM
didi_sht_rcd AS rcd
JOIN
loc_nm_ctg AS ctg1 ON rcd.start_loc = ctg1.loc_nm
JOIN
loc_nm_ctg AS ctg2 ON rcd.end_loc = ctg2.loc_nm
WHERE
ctg1.loc_ctg = '酒店' AND ctg2.loc_ctg = '餐饮'
) AS subquery
GROUP BY
start_loc, end_loc
ORDER BY
trip_count DESC
LIMIT 1
SELECT
start_loc,
end_loc,
COUNT(*) AS trip_count
FROM
(SELECT
start_loc,
end_loc
FROM
didi_sht_rcd AS rcd
JOIN
loc_nm_ctg AS ctg1 ON rcd.start_loc = ctg1.loc_nm
JOIN
loc_nm_ctg AS ctg2 ON rcd.end_loc = ctg2.loc_nm
WHERE
ctg1.loc_ctg = '酒店' AND ctg2.loc_ctg = '餐饮'
) AS subquery
GROUP BY
start_loc, end_loc
ORDER BY
trip_count DESC
SELECT
rcd.cust_uid,
rcd.start_loc,
rcd.end_loc,
rcd.start_tm,
rcd.car_cls
FROM
didi_sht_rcd AS rcd
JOIN
loc_nm_ctg AS ctg1
ON rcd.start_loc = ctg1.loc_nm
JOIN
loc_nm_ctg AS ctg2
ON rcd.end_loc = ctg2.loc_nm
WHERE
(ctg1.loc_ctg = '住宅') AND (ctg2.loc_ctg = '写字楼')
ORDER BY
start_tm ASC
SELECT
rcd.cust_uid,
rcd.start_loc,
rcd.end_loc,
rcd.start_tm,
rcd.car_cls
FROM
didi_sht_rcd AS rcd
LEFT JOIN
loc_nm_ctg AS ctg1
ON
rcd.start_loc = ctg1.loc_nm
LEFT JOIN
loc_nm_ctg AS ctg2
ON rcd.end_loc = ctg2.loc_nm
WHERE
ctg1.loc_ctg = '餐饮' AND ctg2.loc_ctg = '餐饮'
SELECT
local_hour,
COUNT(order_id) AS cnt
FROM(
SELECT
order_id,
MOD(HOUR (call_time) - 3 + 24, 24) AS local_hour
FROM
didi_order_rcd) AS subquery
GROUP BY
local_hour
ORDER BY
cnt DESC
SELECT
local_hour,
COUNT(order_id) AS cnt
FROM(
SELECT
order_id,
HOUR (call_time) - 3 AS local_hour
FROM
didi_order_rcd) AS subquery
GROUP BY
local_hour
ORDER BY
cnt DESC
SELECT
total_orders,
answered_orders,
CONCAT(ROUND(answered_orders/total_orders*100,2),'%') AS answer_rate
FROM
(SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN grab_time = '1970-01-01 00:00:00' THEN 0 ELSE 1 END) AS answered_orders
FROM
didi_order_rcd
WHERE
call_time >='2021-05-03' AND call_time < '2021-05-04'
) AS subquery