WITH tb1 AS( SELECT DATE_FORMAT(event_time, '%Y-%m') AS month, ROUND(SUM(total_amount),0) AS GMV FROM tb_order_overall WHERE total_amount > 0 AND YEAR(event_time) = 2021 GROUP BY month ) SELECT month, GMV FROM tb1 WHERE GMV > 100000 ORDER BY GMV ASC ROUND(SUM(total_amount),0) AS GMV 不能和 WHERE ...

WITH tb1 AS( SELECT tb_user_video_log.video_id, author, DATE_FORMAT(tb_user_video_log.start_time,'%Y-%m') AS month, CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 2 THEN -1 ELSE 0 END AS follow_or_not FROM tb_user_video_log LEFT JOIN tb_video_info ON tb_user_video_log.video_id = tb_video_info.video...

WITH tb1 AS( SELECT tb_user_video_log.video_id, if_retweet, tag FROM tb_user_video_log LEFT JOIN tb_video_info ON tb_user_video_log.video_id = tb_video_info.video_id WHERE DATEDIFF((SELECT MAX(start_time) FROM tb_user_video_log),start_time)<=29 ) SELECT tag, ROUND(SUM(if_retweet),3) AS retweet_cn...

WITH tb1 AS( SELECT tag, tb_user_video_log.video_id, IF(TIMESTAMPDIFF(SECOND,start_time,end_time) > duration,1,TIMESTAMPDIFF(SECOND,start_time,end_time) / duration) AS play_progress FROM tb_user_video_log LEFT JOIN tb_video_info ON tb_user_video_log.video_id = tb_video_info.video_id ) SELECT tag,...

WITH tb1 AS( SELECT tb_user_video_log.video_id, end_time - start_time AS watch_time, duration FROM tb_user_video_log LEFT JOIN tb_video_info ON tb_user_video_log.video_id = tb_video_info.video_id WHERE YEAR(tb_user_video_log.start_time) = 2021 ) SELECT tb1.video_id, ROUND(SUM(if(watch_time >= dur...

WITH tb1 AS ( SELECT driver_id, COUNT(tb_get_car_order.order_id) AS order_num, SUM(fare) AS income FROM tb_get_car_order JOIN tb_get_car_record ON tb_get_car_order.order_id = tb_get_car_record.order_id WHERE DATE(order_time) between '20211001' AND '20211007' AND city = '北京' GROUP BY driver_id HAVING...

WITH tb1 AS( SELECT sales_data.brand_id, sales_data.sales_amount, sales_data.sales_quantity, customer_feedback.satisfaction_score, sales_data.sales_id FROM sales_data JOIN customer_feedback ON sales_data.sales_id = customer_feedback.sales_id WHERE sales_data.sales_month between '2023-01' and '2023-1...

WITH tb1 AS( SELECT suppliers.supplier_id, supply_quality_cost.component_id, suppliers.supplier_name, supply_quality_cost.quality_score, supply_quality_cost.cost FROM supply_quality_cost LEFT JOIN suppliers ON supply_quality_cost.supplier_id = suppliers.supplier_id WHERE quality_score > 80 AND co...

WITH tb1 AS( SELECT author.author_id, author.author_name, COUNT(post.post_id) AS posts_30d, SUM(like_cnt) AS likes_30d FROM post LEFT JOIN author ON post.author_id = author.author_id GROUP BY post.author_id ), tb2 AS( SELECT author_id, author_name, posts_30d, likes_30d, ROUND(likes_30d / posts_30d, ...