데이터 사이언티스트가 마스터해야 할 7가지 분석 패턴

SELECT 
    c.channel_name, 
    r.post_id, 
    r.created_at, 
    r.likes
FROM (
    SELECT 
        channel_id, 
        post_id, 
        created_at, 
        likes,
        DENSE_RANK() OVER(PARTITION BY channel_id ORDER BY likes DESC) AS post_rank
    FROM posts
) AS r
JOIN channels AS c ON r.channel_id = c.channel_id
WHERE r.post_rank <= 5;

SELECT 
    t.month, 
    SUM(t.monthly_revenue) OVER(ORDER BY t.month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3m_revenue
FROM (
    SELECT 
        to_char(created_at::date, 'YYYY-MM') AS month, 
        SUM(amount) AS monthly_revenue
    FROM transactions
    GROUP BY 1
) t;

SELECT 
    inspection_date::DATE, 
    COUNT(violation_id) - LAG(COUNT(violation_id)) OVER(ORDER BY inspection_date::DATE) AS diff
FROM sf_restaurant_health_violations
GROUP BY 1
ORDER BY 1;