SELECT video_id, ROUND(SUM(CASE WHEN visit_time >= duration THEN 1 ELSE 0 END)/COUNT(uid),3) AS avg_comp_play_rate FROM( SELECT uid, t1.video_id, timestampdiff(SECOND, start_time, end_time) AS visit_time, duration FROM tb_user_video_log t1 JOIN tb_video_info t2 ON t1.video_id=t2.video_id WHERE YE...

WITH Active_Date AS( SELECT uid, dt, DENSE_RANK()OVER(PARTITION BY uid ORDER BY dt) rk FROM ((SELECT DISTINCT uid, date(in_time) dt FROM tb_user_log) UNION (SELECT DISTINCT uid, date(out_time) dt FROM tb_user_log) ) sub ), Retention AS( SELECT uid, dt, rk, CASE WHEN DATE_ADD(dt, INTERVAL 1 DAY)=LEAD...

WITH sub AS( SELECT DISTINCT user_id, sales_date FROM sales_tb ), sub2 AS( SELECT user_id, sales_date, date_sub(sales_date, INTERVAL ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY sales_date) DAY) AS consec_date FROM sub ) SELECT user_id, count(*) days_count FROM sub2 GROUP BY user_id HAVING days_co...

WITH sub AS( SELECT DISTINCT sales_date, user_id FROM sales_tb ) SELECT user_id, COUNT(*)+1 FROM( SELECT sales_date, user_id, datediff(sales_date, LAG(sales_date, 1)OVER(PARTITION BY user_id ORDER BY sales_date)) last_date_interval FROM sub ) sub2 WHERE last_date_interval = 1 GROUP BY user_id ORDER ...

WITH sub AS( SELECT dept_no, d. emp_no, salary, RANK()OVER(PARTITION BY dept_no ORDER BY salary DESC) rk FROM dept_emp d JOIN salaries s ON d.emp_no = s.emp_no ) SELECT dept_no, emp_no, salary FROM sub WHERE rk = 1;

SELECT de.emp_no, dm.emp_no manager FROM dept_emp de JOIN dept_manager dm ON de.dept_no = dm.dept_no WHERE de.emp_no != dm.emp_no;

SELECT emp_no, birth_date, first_name, last_name, gender, hire_date FROM( SELECT *, DENSE_RANK()OVER(ORDER BY hire_date DESC) d_rank FROM employees ) sub WHERE d_rank = 3

SELECT emp_no, birth_date, first_name, last_name, gender, hire_date FROM employees WHERE hire_date >= ALL( SELECT hire_date FROM employees )

SELECT vip, SUM(order_price) order_total FROM( SELECT u.user_id, vip, order_id, CASE WHEN order_price IS NULL THEN 0 ELSE order_price END order_price FROM uservip_tb u LEFT JOIN order_tb o ON u.user_id = o.user_id ) sub GROUP BY vip ORDER BY order_total DESC

WITH sub AS( SELECT category, COUNT(DISTINCT oi.order_id) orders_cnt, COUNT(DISTINCT buyer_id) buyers_cnt, SUM(qty) items_qty, SUM(qty*price) revenue, ROUND(SUM(qty * price)/COUNT(DISTINCT oi.order_id), 2) avg_order_value FROM order_items oi JOIN orders o on oi.order_id = o.order_id JOIN product p O...

SELECT vip, COUNT(v.user_id) visit_nums, COUNT(DISTINCT v.user_id) visit_users FROM visit_tb v JOIN uservip_tb u ON v.user_id = u.user_id GROUP BY vip ORDER BY visit_nums DESC

WITH sub AS( SELECT DISTINCT r.user_id, log_time, DAY( DATE_SUB( log_time, INTERVAL DENSE_RANK()OVER(PARTITION BY user_id ORDER BY DAY(log_time)) DAY ) ) consec FROM register_tb r JOIN login_tb l ON r.user_id = l.user_id ) SELECT user_id FROM( SELECT user_id, consec, COUNT(consec) consec_days FROM s...

SELECT post, AVG(ROUND(TIMESTAMPDIFF(second, first_clockin, last_clockin)/3600, 3)) work_hours FROM staff_tb s JOIN attendent_tb a ON s.staff_id = a.staff_id WHERE first_clockin IS NOT NULL AND last_clockin IS NOT NULL GROUP BY post ORDER BY work_hours DESC

SELECT name product_name, total_sales, RANK()OVER(PARTITION BY category ORDER BY total_sales DESC) category_rank FROM (SELECT name, category, p.product_id, SUM(quantity) total_sales FROM products p JOIN orders o ON p.product_id = o.product_id GROUP BY name, category, p.product_id ) q1