Column-Level SQL Lineage Demo
INSERT overwrite TABLE tranfer_statis PARTITION(dt = '20220630') SELECT statis_month, labels, t1.time_types, first_channel, COUNT(t1.uid) new_pc_usr, COUNT(t2.user_id) first_trade_usr, COUNT(if(first_type = 'JJ', t2.user_id, NULL)) fund_first_trade_usr, COUNT(if(first_type = 'ZQ', t2.user_id, NULL)) stock_first_trade_usr, COUNT(t3.user_id) fund_trade_usr, COUNT(t10.user_id) stock_trade_usr, COUNT(t4.user_id) stock_position_usr , NULL, COUNT(t6.user_id) / COUNT(t1.user_no) keep_rate, COUNT(if(t6.user_id IS NULL, t1.user_no, NULL)) loss_usr, COUNT(t7.uid) back_usr, SUM(if(t2.user_id IS NOT NULL, login_days, 0)) / COUNT(t2.user_id) trade_act_days, SUM(if(t2.user_id IS NULL, login_days, 0)) / (COUNT(t1.uid) - COUNT(t2.user_id)) nontrade_act_days, SUM(first_assets_in_amount) / COUNT(t1.uid) avg_amt, COUNT( if(statis_month = substr(trade_date, 1, 7), t1.uid, NULL) ) cur_fst_trade_usr , AVG(trade_amount) avg_trd_amt FROM ( SELECT statis_month, uid, labels, time_types, first_channel, user_no FROM pc_classify ) t1 LEFT JOIN ( SELECT user_id, trade_date, if(small_type = 'FUND', 'JJ', 'ZQ') first_type, trade_amount FROM fund_ipo WHERE trade_date <= '2022-12-30' AND rks = 1 ) t2 ON t1.uid = t2.user_id LEFT JOIN ( SELECT user_id FROM fund_ipo WHERE trade_date <= '2022-12-30' AND small_type = 'FUND' GROUP BY user_id ) t3 ON t1.uid = t3.user_id LEFT JOIN ( SELECT user_id FROM fund_ipo WHERE trade_date <= '2022-12-30' AND small_type <> 'FUND' GROUP BY user_id ) t10 ON t1.uid = t10.user_id LEFT JOIN ( SELECT user_id FROM fact_di WHERE pt_date = '2022-12-30' GROUP BY user_id ) t4 ON t1.uid = t4.user_id LEFT JOIN ( SELECT user_id FROM snapshot_di WHERE pt_date = '2022-12-30' AND total_assets > 0 ) t6 ON t1.uid = t6.user_id LEFT JOIN ( SELECT uid FROM detail_info WHERE dt = '2022-12-30' GROUP BY uid ) t7 ON t1.uid = t7.uid LEFT JOIN ( SELECT user_id, COUNT(DISTINCT pt_date) login_days FROM user_active WHERE pt_date BETWEEN '2022-12-01' AND '2022-12-30' GROUP BY user_id ) t8 ON t1.uid = t8.user_id LEFT JOIN ( SELECT user_id, first_assets_in_amount FROM portrait_di WHERE pt_date = '2022-12-30' ) t9 ON t1.uid = t9.user_id GROUP BY statis_month, labels, t1.time_types, first_channel;
tpcds
example
Submit