sql_training_log/Week_12_SQL/Week_12_day2.sql at main · TomSCarter/sql_training_log · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
WITH base_join AS(), -- rep_id, rep_name, year
aggregates AS(), -- active_customers, revenue
window AS(), -- revenue_rank
SELECT -- 1 row per support rep per calendar year. revenue_change_pct added. Round and order by
-- Decisions: RANK() for ranking, ties allowed. Previous for change means preceding calendar year, null if missing or 0
-- 4min

WITH base_join AS(
    SELECT
        c.SupportRepId AS rep_id,
        e.FirstName || ' ' || e.LastName AS rep_name,
        strftime('%Y', i.InvoiceDate) AS year,
        i.Total,
        c.CustomerId
    FROM
        Invoice i
    INNER JOIN Customer c USING(CustomerId)
    INNER JOIN Employee e ON e.EmployeeId = c.SupportRepId
    ),
aggregates AS(
    SELECT
        rep_id,
        rep_name,
        year,
        SUM(Total) AS revenue,
        COUNT(DISTINCT CustomerId) AS active_customers
    FROM
        base_join
    GROUP BY
        rep_id,
        rep_name,
        year

),
window AS(
    SELECT
        rep_id,
        rep_name,
        year,
        revenue,
        active_customers,
        RANK() OVER(PARTITION BY year ORDER BY revenue) AS revenue_rank,
        revenue - LAG(revenue) OVER(PARTITION BY rep_id ORDER BY year) AS revenue_diff,
        year - LAG(year) OVER(PARTITION BY rep_id ORDER BY year) AS year_diff
    FROM
        aggregates

)
SELECT
        rep_id,
        rep_name,
        year,
        ROUND(revenue, 2) AS revenue,
        active_customers,
        revenue_rank,
        CASE WHEN year_diff <> 1 THEN NULL ELSE ROUND((100 * revenue_diff/revenue), 2) END AS revenue_change_pct
FROM
    window
ORDER BY
    year,
    revenue_rank,
    rep_id;
-- 21 min
-- Ranking is reversed. Percentage uses wrong denominator. Final columns wrong order. Missing nullif

WITH base_join AS(
    SELECT
        c.SupportRepId AS rep_id,
        e.FirstName || ' ' || e.LastName AS rep_name,
        strftime('%Y', i.InvoiceDate) AS year,
        i.Total,
        c.CustomerId
    FROM
        Invoice i
    INNER JOIN Customer c USING(CustomerId)
    INNER JOIN Employee e ON e.EmployeeId = c.SupportRepId
    ),
aggregates AS(
    SELECT
        rep_id,
        rep_name,
        year,
        SUM(Total) AS revenue,
        COUNT(DISTINCT CustomerId) AS active_customers
    FROM
        base_join
    GROUP BY
        rep_id,
        rep_name,
        year

),
window AS(
    SELECT
        rep_id,
        rep_name,
        year,
        revenue,
        active_customers,
        RANK() OVER(PARTITION BY year ORDER BY revenue DESC) AS revenue_rank,
        LAG(revenue) OVER(PARTITION BY rep_id ORDER BY year) AS previous_revenue,
        year - LAG(year) OVER(PARTITION BY rep_id ORDER BY year) AS year_diff
    FROM
        aggregates

)
SELECT
        rep_id,
        rep_name,
        year,
        active_customers,
        ROUND(revenue, 2) AS revenue,
        revenue_rank,
        CASE
            WHEN year_diff = 1 THEN ROUND((100 * (revenue - previous_revenue)/ NULLIF(previous_revenue, 0)), 2)
            END AS revenue_change_pct
FROM
    window
ORDER BY
    year,
    revenue_rank,
    rep_id;