2024

Day 1

Day 1

WITH latest_list AS (
    SELECT
        child_id,
        MAX(submitted_date) AS latest
    FROM wish_lists
    GROUP BY child_id
),

fav_colors AS (
    SELECT
        wl.child_id,
        wl.wishes::json ->> 'colors' AS color_array
    FROM wish_lists AS wl
    INNER JOIN latest_list AS ll ON
        wl.child_id = ll.child_id
        AND wl.submitted_date = ll.latest
),

wishes AS (
    SELECT
        wl.child_id,
        wl.wishes::json ->> 'first_choice' AS primary_wish,
        wl.wishes::json ->> 'second_choice' AS backup_wish,
        (wl.wishes::json -> 'colors')::json ->> 0 AS favorite_color,
        JSON_ARRAY_LENGTH(fc.color_array::json) AS n_colors
    FROM wish_lists AS wl
    INNER JOIN fav_colors AS fc
        ON wl.child_id = fc.child_id
    INNER JOIN latest_list AS ll ON
        wl.child_id = ll.child_id
        AND wl.submitted_date = ll.latest
),

complexity AS (
    SELECT
        toy_name,
        CASE
            WHEN difficulty_to_make = 1 THEN 'Simple Gift'
            WHEN difficulty_to_make = 2 THEN 'Moderate Gift'
            ELSE 'Complex Gift'
        END AS gift_complexity,
        CASE
            WHEN category = 'educational' THEN 'Learning Workshop'
            WHEN category = 'outdoor' THEN 'Outdoor Workshop'
            ELSE 'General Workshop'
        END AS workshop_assignment
    FROM toy_catalogue
)

SELECT
    ch.name,
    wsh.primary_wish,
    wsh.backup_wish,
    wsh.favorite_color,
    wsh.n_colors,
    cmplx.gift_complexity,
    cmplx.workshop_assignment
FROM children AS ch
INNER JOIN wishes AS wsh ON ch.child_id = wsh.child_id
INNER JOIN complexity AS cmplx ON wsh.primary_wish = cmplx.toy_name
ORDER BY ch.name
LIMIT 5;

      name            primary_wish             backup_wish favorite_color
1    Abbie            Barbie dolls Toy musical instruments          Green
2 Abbigail              Jump ropes          Action figures         Yellow
3 Abdullah                  Yo-yos                  Yo-yos         Yellow
4 Abdullah Toy musical instruments            Barbie dolls           Pink
5 Abelardo              Toy trains             Board games         Yellow
  n_colors gift_complexity workshop_assignment
1        5   Moderate Gift    General Workshop
2        1     Simple Gift    Outdoor Workshop
3        1     Simple Gift    General Workshop
4        2    Complex Gift   Learning Workshop
5        4    Complex Gift    General Workshop

0.069 sec elapsed

Day 2

Day 2

WITH combined AS (
    SELECT * FROM letters_a
    UNION ALL
    SELECT * FROM letters_b
),

decoded AS (
    SELECT
        *,
        CHR(value) AS string
    FROM combined
    WHERE
        (value BETWEEN 65 AND 90)
        OR (value BETWEEN 97 AND 122)
        OR (value IN (32, 33, 44, 46))
)

SELECT STRING_AGG(string, '') FROM decoded;

                                                                                           string_agg
1 Dear Santa, I hope this letter finds you well in the North Pole! I want a SQL course for Christmas!

0.006 sec elapsed

Day 3

Day 3

-- noqa: disable=LT05
WITH parsed_menu AS (
    SELECT
        (XPATH('//food_item_id/text()', menu_data))::text [] AS array_food_item,
        CASE
            WHEN ARRAY_LENGTH(XPATH('//total_present/text()', menu_data), 1) > 0
                THEN (XPATH('//total_present/text()', menu_data))[1]::text::integer
            WHEN ARRAY_LENGTH(XPATH('//total_count/text()', menu_data), 1) > 0
                THEN (XPATH('//total_count/text()', menu_data))[1]::text::integer
            WHEN ARRAY_LENGTH(XPATH('//total_guests/text()', menu_data), 1) > 0
                THEN (XPATH('//total_guests/text()', menu_data))[1]::text::integer
        END AS n_guests
    FROM christmas_menus
)

SELECT
    UNNEST(array_food_item) AS food_item,
    COUNT(*) AS n
FROM parsed_menu
WHERE n_guests > 78
GROUP BY food_item
ORDER BY n DESC
LIMIT 1;

  food_item   n
1       493 117

1.568 sec elapsed

Day 4

Day 4

SELECT
    toy_id,
    (
        SELECT COUNT(*)
        FROM (
            SELECT UNNEST(new_tags)
            EXCEPT
            SELECT UNNEST(previous_tags)
        )
    ) AS added,
    (
        SELECT COUNT(*)
        FROM (
            SELECT UNNEST(previous_tags)
            INTERSECT
            SELECT UNNEST(new_tags)
        )
    ) AS unchanged,
    (
        SELECT COUNT(*)
        FROM (
            SELECT UNNEST(previous_tags)
            EXCEPT
            SELECT UNNEST(new_tags)
        )
    ) AS removed
FROM toy_production
ORDER BY added DESC
LIMIT 1;

  toy_id added unchanged removed
1   2726    98         2       0

0.171 sec elapsed

Day 5

Day 5

WITH previous AS (
    SELECT
        production_date,
        toys_produced,
        LAG(toys_produced, 1, NULL)
            OVER (ORDER BY production_date)
        AS previous_day_production
    FROM toy_production
)

SELECT
    tp.production_date,
    pr.toys_produced,
    pr.previous_day_production,
    tp.toys_produced - pr.previous_day_production AS production_change,
    ROUND(
        (tp.toys_produced - pr.previous_day_production)
        / (pr.previous_day_production * 1.0) * 100.0, 2
    ) AS production_change_percentage
FROM toy_production AS tp
INNER JOIN previous AS pr ON tp.production_date = pr.production_date
WHERE pr.previous_day_production IS NOT NULL
ORDER BY production_change_percentage DESC
LIMIT 1;

  production_date toys_produced previous_day_production production_change
1      2017-03-20          2327                       3              2324
  production_change_percentage
1                     77466.67

0.04 sec elapsed

Day 6

Day 6

SELECT
    ch.name AS child_name,
    gi.name AS gift_name,
    gi.price AS gift_price
FROM children AS ch
INNER JOIN gifts AS gi ON ch.child_id = gi.child_id
WHERE gi.price > (SELECT AVG(mp.price) FROM gifts AS mp)
ORDER BY gi.price ASC
LIMIT 5;

  child_name        gift_name gift_price
1     Hobart        art easel     497.44
2    Lindsey wizard chess set     497.52
3       Reba   bubble machine     497.89
4      Reina    talking globe     497.91
5    Brenden    talking globe     498.25

0.037 sec elapsed

Day 7

Day 7

WITH min_experience AS (
    SELECT DISTINCT ON (primary_skill)
        elf_id,
        years_experience,
        primary_skill
    FROM workshop_elves
    ORDER BY primary_skill ASC, years_experience ASC, elf_id ASC
),

max_experience AS (
    SELECT DISTINCT ON (primary_skill)
        elf_id,
        years_experience,
        primary_skill
    FROM workshop_elves
    ORDER BY primary_skill ASC, years_experience DESC, elf_id ASC
)

SELECT
    max_exp.elf_id AS elf_1_id,
    min_exp.elf_id AS elf_2_id,
    max_exp.primary_skill AS shared_skill
FROM min_experience AS min_exp
INNER JOIN max_experience AS max_exp
    ON min_exp.primary_skill = max_exp.primary_skill
ORDER BY min_exp.primary_skill
LIMIT 3;

  elf_1_id elf_2_id  shared_skill
1     4153     3611  Gift sorting
2    10497     1016 Gift wrapping
3       50    13551    Toy making

0.392 sec elapsed

Day 8

Day 8

WITH RECURSIVE managers AS (
    SELECT
        s1.staff_id,
        s1.staff_name,
        s1.manager_id
    FROM staff AS s1
    UNION ALL
    SELECT
        s2.staff_id,
        s2.staff_name,
        s2.manager_id
    FROM staff AS s2
    INNER JOIN managers AS mgmt
        ON s2.manager_id = mgmt.staff_id
)

SELECT COUNT(*) AS n
FROM managers
GROUP BY staff_id
ORDER BY n DESC
LIMIT 1;

   n
1 24

2.382 sec elapsed

Day 9

Day 9

WITH avg_exercise AS (
    SELECT
        reindeer_id,
        exercise_name,
        AVG(speed_record) AS avg_speed
    FROM training_sessions
    GROUP BY reindeer_id, exercise_name
),

max_avg_speed AS (
    SELECT
        reindeer_id,
        MAX(avg_speed) AS max_avg_speed
    FROM avg_exercise
    GROUP BY reindeer_id
)

SELECT
    re.reindeer_name,
    ROUND(mas.max_avg_speed, 2) AS top_speed
FROM max_avg_speed AS mas
INNER JOIN reindeers AS re ON mas.reindeer_id = re.reindeer_id
WHERE re.reindeer_name != 'Rudolph'
ORDER BY top_speed DESC
LIMIT 3;

  reindeer_name top_speed
1         Cupid     88.64
2       Blitzen     88.38
3         Vixen     88.01

0.01 sec elapsed

Day 10

Day 10

WITH drinks AS (
    SELECT
        date,
        SUM(
            CASE WHEN drink_name = 'Eggnog' THEN quantity ELSE 0 END
        ) AS eggnog,
        SUM(
            CASE WHEN drink_name = 'Hot Cocoa' THEN quantity ELSE 0 END
        ) AS hot_cocoa,
        SUM(
            CASE
                WHEN drink_name = 'Peppermint Schnapps' THEN quantity ELSE 0
            END
        ) AS peppermint_schnapps
    FROM drinks
    GROUP BY date
)

SELECT date FROM drinks
WHERE
    1 = 1
    AND eggnog = 198
    AND hot_cocoa = 38
    AND peppermint_schnapps = 298;

        date
1 2024-03-14

0.009 sec elapsed

Day 11

Day 11

SELECT
    field_name,
    harvest_year,
    season,
    ROUND(AVG(trees_harvested) OVER (
        PARTITION BY field_name
        ORDER BY
            harvest_year,
            CASE
                WHEN season = 'Spring' THEN 1
                WHEN season = 'Summer' THEN 2
                WHEN season = 'Fall' THEN 3
                WHEN season = 'Winter' THEN 4
            END
        ROWS BETWEEN 2 PRECEDING
        AND CURRENT ROW
    ), 2) AS moving_ave
FROM
    treeharvests
ORDER BY
    moving_ave DESC
LIMIT 1;

           field_name harvest_year season moving_ave
1 Northern Gardens 62         2024 Winter     327.67

0.009 sec elapsed

Day 12

Day 12

WITH gift_counts AS (
    SELECT
        gi.gift_name,
        COUNT(*) AS n_req
    FROM gift_requests AS req
    LEFT JOIN gifts AS gi ON req.gift_id = gi.gift_id
    GROUP BY gi.gift_name
)

SELECT
    gift_name,
    ROUND(PERCENT_RANK() OVER (
        ORDER BY n_req
    )::numeric, 2) AS percentile
FROM
    gift_counts
ORDER BY percentile DESC, gift_name ASC
LIMIT 20;

                   gift_name percentile
1              costume trunk       0.92
2           finger paint set       0.92
3                 hoverboard       0.92
4                  jump rope       0.92
5                  magic kit       0.92
6           musical keyboard       0.92
7              rocking horse       0.92
8              talking globe       0.92
9              toy submarine       0.92
10             chemistry set       0.86
11 friendship bracelet maker       0.86
12           learning tablet       0.86
13              magic 8 ball       0.86
14                magic wand       0.86
15                toy rocket       0.86
16               bowling set       0.72
17           building blocks       0.72
18       crystal growing kit       0.72
19              flying drone       0.72
20           magic treehouse       0.72

0.035 sec elapsed

Day 13

Day 13

WITH email_addresses AS (
    SELECT
        name,
        email,
        SPLIT_PART(email, '@', 2) AS domain_name
    FROM contact_list, UNNEST(email_addresses) AS email
)

SELECT
    domain_name,
    COUNT(*) AS n_users
FROM
    email_addresses
GROUP BY
    domain_name
ORDER BY n_users DESC
LIMIT 5;

    domain_name n_users
1     bells.org     155
2 stockings.net     154
3     gifts.biz     149
4     bells.com     146
5     holly.biz     145

0.012 sec elapsed

Day 14

Day 14

WITH receipts AS (
    SELECT JSONB_ARRAY_ELEMENTS(cleaning_receipts) AS receipt
    FROM santarecords
),

unnested_receipt_items AS (
    SELECT
        receipt ->> 'drop_off' AS drop_off_date,
        receipt ->> 'garment' AS garment,
        receipt ->> 'color' AS color
    FROM receipts
)

SELECT * FROM unnested_receipt_items
WHERE
    1 = 1
    AND garment = 'suit'
    AND color = 'green';

  drop_off_date garment color
1    2024-12-22    suit green
2    2024-12-22    suit green
3    2024-11-27    suit green
4    2024-11-08    suit green
5    2024-11-28    suit green
6    2024-11-07    suit green
7    2024-11-15    suit green
8    2024-11-06    suit green

0.008 sec elapsed

Day 15

Day 15

WITH last_location AS (
    SELECT coordinate
    FROM sleigh_locations
    WHERE timestamp = (
        SELECT MAX(sl.timestamp) FROM sleigh_locations AS sl
    )
)

SELECT areas.place_name
FROM areas, last_location AS ll
WHERE ST_INTERSECTS(areas.polygon, ll.coordinate);

  place_name
1      Paris

0.337 sec elapsed

Day 16

Day 16

WITH timepoints AS (
    SELECT
        coordinate,
        timestamp AS timestamp_current,
        LAG(timestamp, 1) OVER (ORDER BY timestamp DESC) AS timestamp_next
    FROM sleigh_locations
)

SELECT
    areas.place_name,
    SUM(tp.timestamp_next - tp.timestamp_current) AS duration
FROM timepoints AS tp, areas
WHERE ST_INTERSECTS(areas.polygon, tp.coordinate)
GROUP BY areas.place_name
ORDER BY duration DESC;

   place_name duration
1       Paris 01:30:00
2    New_York 01:15:00
3      London 01:00:00
4 Los_Angeles 01:00:00
5       Tokyo 00:45:00

0.007 sec elapsed

Day 17

Timezones are hard enough, let alone to do them in SQL 😅

Day 18

Couldn’t figure this one out 😕 It asks for a recursive CTE as we used before in Day 8, but this one had a trick to I can’t seem to get

Day 19

Day 19

WITH avg_last_score AS (
    SELECT
        AVG(
            year_end_performance_scores[
                ARRAY_UPPER(year_end_performance_scores, 1)
            ]
        ) AS avg_score
    FROM employees
),

salaries_w_bonus AS (
    SELECT
        CASE
            WHEN
                em.year_end_performance_scores[
                    ARRAY_UPPER(em.year_end_performance_scores, 1)
                ]
                > (SELECT als.avg_score FROM avg_last_score AS als)
                THEN em.salary * 1.15
            ELSE em.salary
        END AS salary_w_bonus
    FROM employees AS em
)

SELECT ROUND(SUM(salary_w_bonus), 2) AS total_salary_paid
FROM salaries_w_bonus;

  total_salary_paid
1        5491552488

0.012 sec elapsed

Day 20

Day 20

SELECT
    url,
    STRING_TO_ARRAY(SPLIT_PART(url, '?', 2), '&') AS query_params,
    CARDINALITY(STRING_TO_ARRAY(SPLIT_PART(url, '?', 2), '&')) AS n_params
FROM web_requests
WHERE
    SPLIT_PART(SUBSTRING(url FROM 'utm_source=[\w-]+'), '=', 2)
    = 'advent-of-sql'
ORDER BY query_params DESC, url ASC
LIMIT 5;

                                                                                                                                                                                                                              url
1                                                   https://prohaska.net?voluptatum_aut_qui=sit_delectus_facere_perferendis&quia_doloremque=deleniti_natus_dolores&ab-quia-pariatur-quisquam=aut_unde_et&utm_source=advent-of-sql
2 https://lebsack.com?voluptatum-sapiente-eaque-et-et=temporibus-assumenda&utm_source=advent-of-sql&sequi-voluptatibus-autem-sequi=cupiditate-consequatur-saepe-nemo-est&placeat-voluptatem-voluptatem=dolorum_laboriosam_aliquid
3                                                   https://heller.com?voluptatum-quam-natus=sunt-dolores-ut-debitis-ab&utm_source=advent-of-sql&utm_source=nisi-soluta&sit_soluta_dolor_voluptatem=qui_ullam_voluptatem_ea_nihil
4                               https://williamson.biz?voluptatum-nihil=perferendis_doloremque&utm_source=advent-of-sql&enim-et=repudiandae-fuga-tempore-commodi-autem&aut-et-est-provident-quae=earum-sit-qui-exercitationem-sed
5             http://koepp.name?voluptatum-et-illo-fuga-suscipit=distinctio-voluptatem-et-consequatur-perferendis&ut_deserunt=aliquid-voluptatum&utm_source=advent-of-sql&expedita-ut-nihil=magni_expedita_distinctio_consequatur
                                                                                                                                                                                                   query_params
1                                                    {voluptatum_aut_qui=sit_delectus_facere_perferendis,quia_doloremque=deleniti_natus_dolores,ab-quia-pariatur-quisquam=aut_unde_et,utm_source=advent-of-sql}
2 {voluptatum-sapiente-eaque-et-et=temporibus-assumenda,utm_source=advent-of-sql,sequi-voluptatibus-autem-sequi=cupiditate-consequatur-saepe-nemo-est,placeat-voluptatem-voluptatem=dolorum_laboriosam_aliquid}
3                                                  {voluptatum-quam-natus=sunt-dolores-ut-debitis-ab,utm_source=advent-of-sql,utm_source=nisi-soluta,sit_soluta_dolor_voluptatem=qui_ullam_voluptatem_ea_nihil}
4                                  {voluptatum-nihil=perferendis_doloremque,utm_source=advent-of-sql,enim-et=repudiandae-fuga-tempore-commodi-autem,aut-et-est-provident-quae=earum-sit-qui-exercitationem-sed}
5           {voluptatum-et-illo-fuga-suscipit=distinctio-voluptatem-et-consequatur-perferendis,ut_deserunt=aliquid-voluptatum,utm_source=advent-of-sql,expedita-ut-nihil=magni_expedita_distinctio_consequatur}
  n_params
1        4
2        4
3        4
4        4
5        4

0.07 sec elapsed

Day 21

Day 21

WITH total_sales AS (
    SELECT
        DATE_PART('YEAR', sale_date) AS sale_year,
        DATE_PART('QUARTER', sale_date) AS sale_quarter,
        SUM(amount) AS sale_amount
    FROM sales
    GROUP BY sale_year, sale_quarter
)

SELECT
    sale_year,
    sale_quarter,
    sale_amount,
    ROUND(
        sale_amount
        / LAG(sale_amount) OVER (ORDER BY sale_year, sale_quarter), 2
    ) AS growth_rate
FROM total_sales
ORDER BY growth_rate DESC NULLS LAST
LIMIT 5;

  sale_year sale_quarter sale_amount growth_rate
1      1997            4      481323        1.40
2      2022            3      460742        1.21
3      2007            2      488715        1.18
4      2000            2      520574        1.18
5      2004            2      501878        1.17

0.015 sec elapsed

Day 22

Day 22

SELECT COUNT(elf_name) AS numofelveswithsql
FROM elves
WHERE 'SQL' = ANY(STRING_TO_ARRAY(skills, ','));

  numofelveswithsql
1              2488

0.023 sec elapsed

Day 23

Day 23

WITH lag_values AS (
    SELECT
        LAG(id, 1) OVER (ORDER BY id) + 1 AS gap_start,
        id - 1 AS gap_end,
        id - LAG(id, 1) OVER (ORDER BY id) AS gap
    FROM sequence_table
),

long_gaps AS (
    SELECT
        gap_start,
        gap_end,
        GENERATE_SERIES(gap_start, gap_end) AS gap_numbers
    FROM lag_values
    WHERE gap > 1
)

SELECT
    gap_start,
    gap_end,
    ARRAY_AGG(gap_numbers ORDER BY gap_start) AS missing_numbers
FROM long_gaps
GROUP BY gap_start, gap_end;

  gap_start gap_end            missing_numbers
1       997    1001    {997,998,999,1000,1001}
2      3761    3765 {3761,3762,3763,3764,3765}
3      6525    6527           {6525,6526,6527}
4      6529    6529                     {6529}
5      9289    9292      {9289,9290,9291,9292}

0.012 sec elapsed

Day 24

Day 24

SELECT
    songs.song_title,
    COUNT(songs.song_title) AS total_plays,
    SUM(
        CASE WHEN up.duration < songs.song_duration THEN 1 ELSE 0 END
    ) AS skipped
FROM songs
INNER JOIN user_plays AS up ON songs.song_id = up.song_id
GROUP BY songs.song_title
ORDER BY total_plays DESC, skipped ASC
LIMIT 10;

                               song_title total_plays skipped
1         All I Want For Christmas Is You         562     412
2                          The First Noel         541     416
3                            Jingle Bells         524     399
4                          Deck the Halls         521     395
5                            Silent Night         513     389
6                       Winter Wonderland         511     386
7                            Silver Bells         508     386
8  Let It Snow! Let It Snow! Let It Snow!         506     381
9                         White Christmas         504     382
10                         Last Christmas         503     385

0.011 sec elapsed