SQL 학습 - JOIN (TIL 230511)

📚 KDT WEEK 6 DAY 4 TIL

• JOIN

 

---

숙제

%%sql

SELECT  

    LEFT(ts.ts, 7) as month,

    ch.channel,

    COUNT(DISTINCT ch.userid) as uniqueUsers,

    COUNT(DISTINCT CASE WHEN trans.amount > 0 THEN ch.userid END) as paidUsers,

    ROUND(paidUsers*100.0 / NULLIF(uniqueUsers,0), 2) as conversionRate,

    SUM(CASE WHEN trans.refunded is not NULL THEN trans.amount END) as grossRevenue,

    SUM(CASE WHEN trans.refunded is False THEN trans.amount END) as netRevenue

FROM raw_data.session_timestamp as ts

INNER JOIN raw_data.user_session_channel as ch ON ts.sessionid = ch.sessionid

LEFT JOIN raw_data.session_transaction as trans ON ch.sessionid = trans.sessionid

GROUP BY 1,2

ORDER BY 1,2 --CREATE할 때에는 의미없음, DB가 알아서 함