2025

Day 10

Day 10

SELECT
    TRIM(LOWER(raw_wish)) AS wish,
    COUNT(*) AS count
FROM wish_list
GROUP BY wish
ORDER BY count DESC
LIMIT 20;

                          wish count
1             lego city f1 car 32893
2            barbie dreamhouse 32785
3                 nerf blaster 32746
4           lego star wars set 32611
5        beyblade battle arena 29564
6    magna-tiles pet playhouse 29529
7   bluey supermarket play set 26292
8  lego friends amusement park 25982
9          pokemon trainer box 25968
10          duplo building set 23005
11 mini brands fill the fridge 22965
12      electric toy train set 22885
13       toniebox audio player 19529
14                     scooter 19496
15                  vr headset 16468
16               squishmallows 16304
17          shaved ice machine 16263
18              drone for kids 13151
19                coding robot 13025
20                  headphones 13006

0.109 sec elapsed

Day 11

Day 11

SELECT
    cat.official_category,
    SUM(inv.quantity) AS total_usable_snowballs
FROM snowball_categories AS cat
LEFT JOIN snowball_inventory AS inv
    ON
        cat.official_category = inv.category_name
        AND inv.quantity >= 0
GROUP BY cat.official_category
ORDER BY total_usable_snowballs ASC;

          official_category total_usable_snowballs
1      arctic blast premium                  19335
2  everfrost training round                  39612
3 polar precision microball                 117912
4       glacier sphere (xl)                 275503
5       north ridge compact                 991208
6       frost-flight deluxe                1586029

0.119 sec elapsed

Day 12

Day 12

UPDATE hotline_messages
SET status = 'approved'
WHERE
    1 = 1
    AND LOWER(transcript) LIKE '%sorry%'
    AND status IS NULL;

DELETE FROM hotline_messages
WHERE
    1 = 1
    AND tag IN (
        'penguin prank',
        'time-loop advisory',
        'possible dragon',
        'nonsense alert'
    )
    OR LOWER(caller_name) = 'test caller';

SELECT
    COALESCE(status, 'needs review') AS imputed_status,
    COUNT(*) AS n_messages
FROM hotline_messages
GROUP BY imputed_status;

UPDATE successfully executed. 68 rows were affected.


DELETE successfully executed. 89 rows were affected.


  imputed_status n_messages
1   needs review        501
2       approved        477

0.022 sec elapsed

Day 13

Day 13

WITH os AS (
    SELECT
        volunteer_name,
        role AS assigned_role,
        CASE
            WHEN shift_time LIKE '%10%' THEN '10:00'
            WHEN shift_time LIKE '%12%' THEN '12:00'
            WHEN shift_time LIKE '%2%' THEN '14:00'
        END AS shift_time
    FROM official_shifts
),

lms AS (
    SELECT
        volunteer_name,
        CASE
            WHEN LOWER(assigned_task) LIKE '%stage%' THEN 'stage_setup'
            WHEN LOWER(assigned_task) LIKE '%cocoa%' THEN 'cocoa_station'
            WHEN LOWER(assigned_task) LIKE '%parking%' THEN 'parking_support'
            WHEN LOWER(assigned_task) LIKE '%choir%' THEN 'choir_assistant'
            WHEN
                LOWER(assigned_task) LIKE '%handwarmer%'
                THEN 'handwarmer_handout'
            WHEN LOWER(assigned_task) LIKE '%snow%' THEN 'snow_shoveling'
            WHEN LOWER(assigned_task) LIKE '%shovel%' THEN 'snow_shoveling'
        END AS assigned_task_clean,
        CASE
            WHEN LOWER(time_slot) LIKE '%noon%' THEN '12:00'
            WHEN time_slot LIKE '%10%' THEN '10:00'
            WHEN time_slot LIKE '%2%' THEN '14:00'
        END AS time_slot_clean
    FROM last_minute_signups
),

merged_table AS (
    SELECT
        COALESCE(lms.volunteer_name, os.volunteer_name) AS volunteer_name,
        COALESCE(lms.assigned_task_clean, os.assigned_role) AS assigned_role,
        COALESCE(lms.time_slot_clean, os.shift_time) AS shift_time
    FROM lms
    FULL OUTER JOIN os ON lms.volunteer_name = os.volunteer_name
)

SELECT DISTINCT
    volunteer_name,
    assigned_role,
    shift_time
FROM merged_table
ORDER BY volunteer_name ASC
LIMIT 15;

      volunteer_name      assigned_role shift_time
1       Aaron Carter    parking_support      14:00
2         Aaron Diaz    choir_assistant      14:00
3        Aaron Evans      cocoa_station      14:00
4      Aaron Francis handwarmer_handout      14:00
5  Abigail Hernandez        stage_setup      10:00
6          Adam King        stage_setup      10:00
7        Adil Foster        stage_setup      14:00
8        Adil Rahman        stage_setup      14:00
9         Adrian Cox      cocoa_station      10:00
10     Aisha Bennett      cocoa_station      12:00
11        Aisha Khan    choir_assistant      12:00
12    Aisha Mohammed      cocoa_station      14:00
13       Alina Popov      cocoa_station      12:00
14      Alina Powell    parking_support      12:00
15      Amina Diallo    parking_support      14:00

0.002 sec elapsed

Day 14

Day 14

WITH total_counts AS (
    SELECT
        user_name,
        artist,
        COUNT(artist) AS count
    FROM listening_logs
    WHERE played_at > '2025-01-01'
    GROUP BY user_name, artist
)

SELECT
    user_name,
    artist,
    count
FROM
    (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY
                user_name
            ORDER BY count DESC) AS user_count
        FROM total_counts
    ) AS tc
WHERE user_count < 4
ORDER BY user_name ASC, count DESC
LIMIT 24;

           user_name                       artist count
1  Abigail Hernandez                   Ed Sheeran    78
2  Abigail Hernandez                 Rotten Mango    15
3  Abigail Hernandez                Billie Eilish     4
4         Adrian Cox               Kendrick Lamar   128
5         Adrian Cox        Stuff You Should Know    30
6         Adrian Cox                   Pentatonix     6
7        Alex Rivera                   Ed Sheeran   274
8        Alex Rivera Call Her Daddy (Alex Cooper)    42
9        Alex Rivera                    Green Day    11
10    Anders Nilsson                  Snow Patrol   101
11    Anders Nilsson                    SmartLess    29
12    Anders Nilsson                    Blink-182     5
13      Anthony King                   Pentatonix   114
14      Anthony King      The Tucker Carlson Show    14
15      Anthony King                  The Beatles     5
16       Anya Pavlov                     Coldplay   135
17       Anya Pavlov   Just Creepy: Scary Stories    19
18       Anya Pavlov                Ariana Grande     5
19       Ava Morales            Angels & Airwaves    90
20       Ava Morales   Just Creepy: Scary Stories    23
21       Ava Morales                 Foo Fighters     5
22    Benjamin Davis                     Coldplay    93
23    Benjamin Davis                Billie Eilish     6
24    Benjamin Davis             Mostly Technical     6

0.011 sec elapsed

Day 15

Day 15

WITH dates AS (
    SELECT DISTINCT gift_date AS delivery_date
    FROM deliveries_assigned
    WHERE gift_date > '2025-12-14'
    ORDER BY delivery_date
),

expected_delivery_dates AS (
    SELECT
        fam.id AS family_id,
        fam.family_name,
        dt.delivery_date
    FROM families AS fam
    CROSS JOIN dates AS dt
)

SELECT
    edd.delivery_date AS unassigned_date,
    edd.family_name
FROM expected_delivery_dates AS edd
LEFT JOIN deliveries_assigned AS del
    ON
        edd.delivery_date = del.gift_date
        AND edd.family_id = del.family_id
WHERE del.id IS NULL
GROUP BY unassigned_date, edd.family_name
ORDER BY unassigned_date ASC, edd.family_name ASC
LIMIT 25;

   unassigned_date     family_name
1       2025-12-15      Adil Rossi
2       2025-12-15    Aisha Connor
3       2025-12-15     Amina Perez
4       2025-12-15      Amina Wong
5       2025-12-15    Andre Flores
6       2025-12-15      Anya Singh
7       2025-12-15      Arjun Wong
8       2025-12-15   Bianca Connor
9       2025-12-15    Caleb Petrov
10      2025-12-15   Caleb Roberts
11      2025-12-15   Carmen Carter
12      2025-12-15   Carmen Garcia
13      2025-12-15    Casey Flores
14      2025-12-15      Chi Hughes
15      2025-12-15   Clara Johnson
16      2025-12-15   Dara Bautista
17      2025-12-15   David Ramirez
18      2025-12-15    Elias Petrov
19      2025-12-15    Ethan Flores
20      2025-12-15    Eva Gonzalez
21      2025-12-15      Eva Turner
22      2025-12-15     Haruto Diaz
23      2025-12-15      Hugo Perez
24      2025-12-15       Hugo Wong
25      2025-12-15 Isaac Andersson

0.005 sec elapsed

Day 16

Day 16

WITH available_cars AS (
    SELECT
        car_id,
        available_mixins
    FROM cocoa_cars
    ORDER BY total_stock DESC
    LIMIT 3
),

available_mixins AS (
    SELECT
        car_id,
        UNNEST(available_mixins) AS available_mixins
    FROM available_cars
),

passengers_mixins AS (
    SELECT
        passenger_name,
        UNNEST(favorite_mixins) AS favorite_mixins
    FROM passengers
)

SELECT
    pm.passenger_name,
    ARRAY_AGG(DISTINCT am.car_id) AS cars_available
FROM passengers_mixins AS pm
INNER JOIN available_mixins AS am
    ON pm.favorite_mixins = am.available_mixins
GROUP BY pm.passenger_name
ORDER BY pm.passenger_name ASC
LIMIT 25;

         passenger_name cars_available
1        Aaron Ashcroft            {2}
2   Abigail Worthington          {2,9}
3       Aiden Stanfield            {9}
4    Alexander Stratton          {5,9}
5      Alexis Thorndale            {9}
6    Alice Merriweather        {2,5,9}
7        Amanda Fitzroy        {2,5,9}
8       Amelia Rosewood            {9}
9        Anna Westfield            {2}
10       Aria Blackwood        {2,5,9}
11     Ashley Fairhaven        {2,5,9}
12     August Blackwell        {2,5,9}
13     Aurora Whitfield        {2,5,9}
14 Benjamin Fairweather            {9}
15       Benjamin Patel            {9}
16       Bianca Pereira            {9}
17        Blake Dunwood          {5,9}
18     Brandon Locklear        {2,5,9}
19           Caleb Osei          {5,9}
20    Cameron Gladstone          {2,9}
21       Cameron Rogers        {2,5,9}
22        Carlos Mendez          {5,9}
23   Caroline Bannister          {2,9}
24    Carter Brookfield            {2}
25      Charlotte Singh        {2,5,9}

0.003 sec elapsed

Day 17

Day 17

WITH last_score AS (
    SELECT MAX(id) AS last_id
    FROM price_changes
    GROUP BY product_id
),

price_calculations AS (
    SELECT
        pid.id,
        pd.product_name,
        pid.price AS current_price,
        LAG(pid.price)
            OVER (
                PARTITION BY pd.product_name
                ORDER BY pid.effective_timestamp ASC
            )
            AS previous_price
    FROM price_changes AS pid
    INNER JOIN products AS pd
        ON pid.product_id = pd.product_id
)

SELECT
    product_name,
    current_price,
    previous_price,
    current_price - previous_price AS price_diff
FROM price_calculations
WHERE
    id IN (SELECT ls.last_id FROM last_score AS ls)
ORDER BY product_name ASC
LIMIT 25;

                      product_name current_price previous_price price_diff
1                   Classic Candle         49.55          57.05      -7.50
2    Classic Cookie Decorating Kit        243.09         214.65      28.44
3             Classic Cookware Set         66.24          79.06     -12.82
4            Classic Festive Scarf         67.10          61.20       5.90
5         Classic Holiday Jam Trio         15.99          18.89      -2.90
6                      Classic Mug        173.05         157.02      16.03
7                  Classic Notepad        120.49         122.23      -1.74
8                 Classic Ornament         35.05          30.25       4.80
9      Classic Pecan Praline Bites         45.42          41.74       3.68
10         Classic Roasted Cashews        257.33         223.63      33.70
11                    Classic Sled        193.40         166.11      27.29
12 Classic White Chocolate Popcorn         40.38          47.51      -7.13
13             Cozy Almond Brittle        107.21         116.42      -9.21
14                 Cozy Board Game         39.20          45.96      -6.76
15                     Cozy Candle        105.68          91.49      14.19
16               Cozy Cookware Set        106.87          89.35      17.52
17           Cozy Fruit Assortment         30.08          25.79       4.29
18                       Cozy Lamp        138.36         170.15     -31.79
19                    Cozy Notepad        102.78         106.04      -3.26
20        Cozy Pecan Praline Bites         22.24          18.69       3.55
21                     Cozy Puzzle        209.20         227.24     -18.04
22          Cozy Reindeer Headband        215.40         196.31      19.09
23        Cozy Scented Pine Sachet        152.76         159.88      -7.12
24             Cozy Trail Mix Trio        107.81         101.09       6.72
25    Cozy White Chocolate Popcorn         74.78          71.58       3.20

0.005 sec elapsed

Day 18

Day 18

WITH latest_order AS (
    SELECT
        customer_id,
        MAX(created_at) AS latest_date
    FROM orders
    GROUP BY customer_id
)

SELECT
    ord.customer_id,
    ((ord.order_data::json ->> 'gift')::json ->> 'wrapped')::boolean
        AS gift_wrap,
    (ord.order_data::json ->> 'shipping')::json ->> 'method' AS shipping_method,
    (ord.order_data::json ->> 'risk')::json ->> 'flag' AS risk
FROM orders AS ord
INNER JOIN
    latest_order AS lo
    ON
        ord.customer_id = lo.customer_id
        AND ord.created_at = lo.latest_date

   customer_id gift_wrap shipping_method   risk
1            1     FALSE         express   <NA>
2            2     FALSE        standard   <NA>
3            3      TRUE        standard   high
4            4     FALSE         express   <NA>
5            5      TRUE       overnight   <NA>
6            6      TRUE       overnight   <NA>
7            7     FALSE         express   <NA>
8            8     FALSE       overnight   <NA>
9            9      TRUE         express   <NA>
10          10     FALSE        standard medium
11          11      TRUE        standard medium
12          12      TRUE        standard medium
13          13      TRUE        standard   <NA>
14          14      TRUE        standard   <NA>
15          15     FALSE         express medium
16          16     FALSE         express   <NA>
17          17     FALSE        standard   <NA>
18          18     FALSE       overnight    low
19          19     FALSE        standard   <NA>
20          20     FALSE       overnight   <NA>
21          21      TRUE       overnight   <NA>
22          22     FALSE        standard medium
23          23     FALSE         express   <NA>
24          24     FALSE       overnight   <NA>
25          25      TRUE       overnight   high
26          26     FALSE        standard    low
27          27      TRUE        standard   <NA>
28          28     FALSE        standard    low
29          29      TRUE       overnight   high
30          30     FALSE       overnight medium
31          31     FALSE         express   <NA>
32          32     FALSE       overnight   <NA>
33          33      TRUE         express   <NA>
34          34     FALSE         express   <NA>
35          35      TRUE         express   high
36          36      TRUE       overnight   <NA>
37          37      TRUE       overnight   <NA>
38          38      TRUE        standard   <NA>
39          39      TRUE       overnight   <NA>
40          40     FALSE         express   <NA>
41          41     FALSE        standard   high
42          42     FALSE       overnight medium
43          43      TRUE         express   <NA>
44          44      TRUE        standard   <NA>
45          45     FALSE        standard   <NA>
46          46     FALSE        standard   <NA>
47          47     FALSE        standard   <NA>
48          48      TRUE        standard   <NA>
49          49      TRUE         express   high
50          50      TRUE         express    low

0.004 sec elapsed

Day 19

Day 19

INSERT INTO misdelivered_presents
SELECT
    id,
    child_name,
    delivery_location,
    gift_name,
    scheduled_at,
    DATE_TRUNC('minute', NOW()) AS flagged_at,
    'Invalid delivery location' AS reason
FROM deliveries
WHERE
    delivery_location IN (
        'Volcano Rim', 'Drifting Igloo', 'Abandoned Lighthouse', 'The Vibes'
    );

SELECT
    id,
    child_name,
    delivery_location,
    gift_name,
    scheduled_at,
    flagged_at,
    reason
FROM misdelivered_presents
WHERE flagged_at = DATE_TRUNC('minute', NOW())
ORDER BY id
LIMIT 25;

INSERT successfully executed. 103 rows were affected.

    id child_name    delivery_location            gift_name        scheduled_at
1    3    Mila N.            The Vibes storybook collection 2025-12-24 21:09:00
2   22    Lena F. Abandoned Lighthouse       plush reindeer 2025-12-24 19:08:00
3   23    Mila N. Abandoned Lighthouse storybook collection 2025-12-24 20:42:00
4   29   Mateo C.          Volcano Rim       plush reindeer 2025-12-24 21:44:00
5   31     Nia G.       Drifting Igloo            robot toy 2025-12-24 19:57:00
6   38    Omar Q.            The Vibes          science kit 2025-12-24 19:48:00
7   41    Hiro T.            The Vibes     wooden train set 2025-12-24 21:04:00
8   47    Omar Q.          Volcano Rim         art supplies 2025-12-24 20:09:00
9   53     Ava J.          Volcano Rim       plush reindeer 2025-12-24 18:20:00
10  59   Sofia K. Abandoned Lighthouse            robot toy 2025-12-24 20:46:00
11  62    Lena F.       Drifting Igloo     wooden train set 2025-12-24 20:21:00
12  68    Mila N.          Volcano Rim           teddy bear 2025-12-24 20:01:00
13  70   Mateo C. Abandoned Lighthouse storybook collection 2025-12-24 19:50:00
14  72    Zara S.            The Vibes     wooden train set 2025-12-24 20:47:00
15  85    Mila N. Abandoned Lighthouse           board game 2025-12-24 21:03:00
16  87   Jonah W.       Drifting Igloo     wooden train set 2025-12-24 21:08:00
17  92   Layla B.            The Vibes storybook collection 2025-12-24 21:40:00
18  94   Layla B.       Drifting Igloo       plush reindeer 2025-12-24 20:32:00
19  96    Omar Q.          Volcano Rim           teddy bear 2025-12-24 20:13:00
20  99    Ravi P. Abandoned Lighthouse          science kit 2025-12-24 20:45:00
21 111   Sofia K. Abandoned Lighthouse         art supplies 2025-12-24 18:53:00
22 116    Lena F.          Volcano Rim          science kit 2025-12-24 19:07:00
23 120    Zara S.       Drifting Igloo           puzzle box 2025-12-24 20:21:00
24 123    Lena F.            The Vibes       plush reindeer 2025-12-24 19:03:00
25 125    Hiro T.            The Vibes            robot toy 2025-12-24 21:11:00
            flagged_at                    reason
1  2025-12-20 17:06:00 Invalid delivery location
2  2025-12-20 17:06:00 Invalid delivery location
3  2025-12-20 17:06:00 Invalid delivery location
4  2025-12-20 17:06:00 Invalid delivery location
5  2025-12-20 17:06:00 Invalid delivery location
6  2025-12-20 17:06:00 Invalid delivery location
7  2025-12-20 17:06:00 Invalid delivery location
8  2025-12-20 17:06:00 Invalid delivery location
9  2025-12-20 17:06:00 Invalid delivery location
10 2025-12-20 17:06:00 Invalid delivery location
11 2025-12-20 17:06:00 Invalid delivery location
12 2025-12-20 17:06:00 Invalid delivery location
13 2025-12-20 17:06:00 Invalid delivery location
14 2025-12-20 17:06:00 Invalid delivery location
15 2025-12-20 17:06:00 Invalid delivery location
16 2025-12-20 17:06:00 Invalid delivery location
17 2025-12-20 17:06:00 Invalid delivery location
18 2025-12-20 17:06:00 Invalid delivery location
19 2025-12-20 17:06:00 Invalid delivery location
20 2025-12-20 17:06:00 Invalid delivery location
21 2025-12-20 17:06:00 Invalid delivery location
22 2025-12-20 17:06:00 Invalid delivery location
23 2025-12-20 17:06:00 Invalid delivery location
24 2025-12-20 17:06:00 Invalid delivery location
25 2025-12-20 17:06:00 Invalid delivery location

0.009 sec elapsed

Day 20

I think Aaron forgot to upload the inserts for this day, since it was not available for download. And I was too lazy to use the online playground thingy.

Day 21

Day 22

Day 23

Day 24

Day 25