Advent of SQL, Day 9 - Evergreen Market Orders

We are on day 9 of advent of SQL, and I feel good so far.

Let’s see what we learn today?

Let’s get the inserts for the day.

sqlite> .read day9-inserts.sql
sqlite> .schema
CREATE TABLE orders (
    id           INT PRIMARY KEY,
    customer_id  INT,
    created_at   TIMESTAMP,
    order_data   JSONB
);
sqlite> .mode table
sqlite> SELECT * FROM orders limit 10;
+----+-------------+---------------------+--------------------------------------------------------------+
| id | customer_id |     created_at      |                          order_data                          |
+----+-------------+---------------------+--------------------------------------------------------------+
| 1  | 1           | 2025-11-21 13:08:22 | {"shipping": {"method": "standard"}, "gift": {"wrapped": tru |
|    |             |                     | e}}                                                          |
+----+-------------+---------------------+--------------------------------------------------------------+
| 2  | 1           | 2025-11-21 18:42:58 | {"shipping": {"method": "overnight"}, "risk": {"flag": "high |
|    |             |                     | "}, "gift": {"wrapped": false}}                              |
+----+-------------+---------------------+--------------------------------------------------------------+
| 3  | 1           | 2025-11-21 21:01:46 | {"shipping": {"method": "standard"}, "gift": {"wrapped": fal |
|    |             |                     | se}}                                                         |
+----+-------------+---------------------+--------------------------------------------------------------+
| 4  | 1           | 2025-11-24 13:17:27 | {"shipping": {"method": "standard"}, "gift": {"wrapped": tru |
|    |             |                     | e}}                                                          |
+----+-------------+---------------------+--------------------------------------------------------------+
| 5  | 1           | 2025-11-24 21:09:46 | {"shipping": {"method": "overnight"}, "gift": {"wrapped": fa |
|    |             |                     | lse}}                                                        |
+----+-------------+---------------------+--------------------------------------------------------------+
| 6  | 1           | 2025-11-25 07:24:55 | {"shipping": {"method": "standard"}, "risk": {"flag": "mediu |
|    |             |                     | m"}, "gift": {"wrapped": true}}                              |
+----+-------------+---------------------+--------------------------------------------------------------+
| 7  | 1           | 2025-11-25 17:42:36 | {"shipping": {"method": "standard"}, "gift": {"wrapped": fal |
|    |             |                     | se}}                                                         |
+----+-------------+---------------------+--------------------------------------------------------------+
| 8  | 1           | 2025-11-27 02:34:24 | {"shipping": {"method": "express"}, "gift": {"wrapped": true |
|    |             |                     | }}                                                           |
+----+-------------+---------------------+--------------------------------------------------------------+
| 9  | 1           | 2025-11-30 22:43:54 | {"shipping": {"method": "overnight"}, "gift": {"wrapped": tr |
|    |             |                     | ue}}                                                         |
+----+-------------+---------------------+--------------------------------------------------------------+
| 10 | 1           | 2025-12-01 04:03:33 | {"shipping": {"method": "express"}, "risk": {"flag": "medium |
|    |             |                     | "}, "gift": {"wrapped": false}}                              |
+----+-------------+---------------------+--------------------------------------------------------------+
sqlite> 

Looks like we will deal with JSON today, seems exciting. I haven’t dealt with JSON in SQLite yet, today will break it.

Advent of SQL - Day 8, Product Catalog

Whopsies! This is day 8.

Let’s get straigh…

HOOH! We need to clean up some SQL for running in SQLite.

sed -i 's/TIMESTAMP[[:space:]]*//g' day8-inserts-sqlite.sql

Just cleaning up TIMESTAMP in INSERT before the date value.

Here we go: The SQL to run in SQLite.

DROP TABLE IF EXISTS price_changes;
DROP TABLE IF EXISTS products;

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name TEXT
);

CREATE TABLE price_changes (
    id INT PRIMARY KEY,
    product_id INT,
    price NUMERIC(10,2),
    effective_timestamp 
);

INSERT INTO products (product_id, product_name) VALUES
    (1, 'Deluxe Sled'),
    (2, 'Holiday Trail Mix Trio'),
    (3, 'Premium Cinnamon Roasted Almonds'),
    (4, 'Deluxe Wrapping Paper'),
    (5, 'Deluxe Roasted Cashews'),
    (6, 'Festive Cookware Set'),
    (7, 'Deluxe Mug'),
    (8, 'Premium Sled'),
    (9, 'Essential Sled'),
    (10, 'Family Snow Boots'),
    (11, 'Family Dark Chocolate Almonds'),
    (12, 'Premium Festive Scarf'),
    (13, 'Essential Cookie Decorating Kit'),
    (14, 'Festive White Chocolate Popcorn'),
    (15, 'Cozy Puzzle'),
    (16, 'Holiday Cheddar Popcorn'),
    (17, 'Premium Board Game'),
    (18, 'Deluxe Pecan Praline Bites'),
    (19, 'Cozy Almond Brittle'),
    (20, 'Winter Sled');

INSERT INTO price_changes (id, product_id, price, effective_timestamp) VALUES
    (1, 1, 148.28, '2025-12-01 05:25:35'),
    (2, 1, 148.63, '2025-12-02 18:15:33'),
    (3, 1, 126.78, '2025-12-02 18:40:38'),
    (4, 1, 119.12, '2025-12-03 10:14:35'),
    (5, 1, 98.57, '2025-12-04 04:14:31'),
    (6, 1, 88.49, '2025-12-06 19:02:40'),
    (7, 1, 80.88, '2025-12-07 10:43:54'),
    (8, 1, 78.88, '2025-12-08 06:45:39'),
    (9, 1, 80.24, '2025-12-08 16:11:11'),
    (10, 1, 73.9, '2025-12-10 14:33:43'),
    (11, 1, 88.2, '2025-12-12 02:21:09'),
    (12, 1, 99.03, '2025-12-12 02:58:14'),
    (13, 1, 100.18, '2025-12-14 15:58:03'),
    (14, 1, 106.91, '2025-12-16 01:51:05'),
    (15, 1, 109.25, '2025-12-16 16:01:53'),
    (16, 2, 29.54, '2025-12-03 14:21:10'),
    (17, 2, 34.33, '2025-12-03 19:14:31'),
    (18, 2, 39.08, '2025-12-04 06:13:48'),
    (19, 2, 32.71, '2025-12-04 18:33:17'),
    (20, 2, 31.71, '2025-12-05 22:36:14'),
    (21, 2, 28.88, '2025-12-06 02:42:02'),
    (22, 2, 23.14, '2025-12-07 09:46:54'),
    (23, 2, 25.65, '2025-12-07 10:03:45'),
    (24, 2, 27.06, '2025-12-07 14:39:15'),
    (25, 2, 24.48, '2025-12-07 20:08:05'),
    (26, 2, 26.84, '2025-12-09 07:44:32'),
    (27, 2, 27.39, '2025-12-13 06:25:19'),
    (28, 2, 26.6, '2025-12-14 10:16:34'),
    (29, 2, 21.38, '2025-12-15 16:20:20'),
    (30, 2, 17.75, '2025-12-16 09:28:13');

We can get started.

Advent of SQL, Day 7 - Polar Express

There were a few things, I had to dig up for converting the JSON ARRAY[] in the statements into strings for SQLite, we can’t really use list of strings in SQLite.

Here’s the command to convert that array of strings into string.

sed "s/ARRAY\['/'\[\"/g; s/','/\",\"/g; s/']/\"]'/g" day7-inserts.sql > day7-inserts-sqlite.sql

OK, once that’s done, this can be safely run into a sqlite database.

$ sqlite3
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read day7-inserts-sqlite.sql
sqlite> .schema
CREATE TABLE passengers (
    passenger_id INT PRIMARY KEY,
    passenger_name TEXT,
    favorite_mixins TEXT[],
    car_id INT
);
CREATE TABLE cocoa_cars (
    car_id INT PRIMARY KEY,
    available_mixins TEXT[],
    total_stock INT
);
sqlite> 
sqlite> .mode table
sqlite> select * from passengers limit 20;
+--------------+----------------+--------------------------------------------------------------+--------+
| passenger_id | passenger_name |                       favorite_mixins                        | car_id |
+--------------+----------------+--------------------------------------------------------------+--------+
| 1            | Ava Johnson    | ["vanilla foam"]                                             | 2      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 2            | Mateo Cruz     | ["caramel drizzle","shaved chocolate","white chocolate"]     | 2      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 3            | Nia Grant      | ["shaved chocolate"]                                         | 5      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 4            | Hiro Tanaka    | ["shaved chocolate"]                                         | 2      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 5            | Layla Brooks   | ["crispy rice","dark chocolate","caramel drizzle","cinnamon" | 3      |
|              |                | ]                                                            |        |
+--------------+----------------+--------------------------------------------------------------+--------+
| 6            | Ravi Patel     | ["caramel drizzle","shaved chocolate","white chocolate"]     | 5      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 7            | Sofia Kim      | ["cinnamon"]                                                 | 9      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 8            | Jonah Wolfe    | ["cinnamon","dark chocolate"]                                | 7      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 9            | Elena Morales  | ["white chocolate","shaved chocolate","caramel drizzle"]     | 6      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 10           | Diego Ramos    | ["shaved chocolate"]                                         | 1      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 11           | Zara Sheikh    | ["vanilla foam","crispy rice","peppermint"]                  | 4      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 12           | Caleb Osei     | ["shaved chocolate","dark chocolate","white chocolate"]      | 8      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 13           | Mila Novak     | ["crispy rice","cinnamon"]                                   | 4      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 14           | Lucas Ford     | ["vanilla foam","white chocolate","cinnamon"]                | 4      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 15           | Yara Haddad    | ["white chocolate","dark chocolate"]                         | 2      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 16           | Omar Qureshi   | ["marshmallow"]                                              | 3      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 17           | Keiko Ito      | ["vanilla foam","marshmallow"]                               | 7      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 18           | Tariq Hassan   | ["dark chocolate","crispy rice","white chocolate","peppermin | 2      |
|              |                | t"]                                                          |        |
+--------------+----------------+--------------------------------------------------------------+--------+
| 19           | Mira Zhao      | ["caramel drizzle","marshmallow","cinnamon"]                 | 7      |
+--------------+----------------+--------------------------------------------------------------+--------+
| 20           | Bianca Pereira | ["dark chocolate","peppermint"]                              | 5      |
+--------------+----------------+--------------------------------------------------------------+--------+
sqlite> 
sqlite> select * from cocoa_cars;
+--------+--------------------------------------------------------------+-------------+
| car_id |                       available_mixins                       | total_stock |
+--------+--------------------------------------------------------------+-------------+
| 5      | ["white chocolate","shaved chocolate"]                       | 412         |
+--------+--------------------------------------------------------------+-------------+
| 2      | ["cinnamon","marshmallow","caramel drizzle"]                 | 359         |
+--------+--------------------------------------------------------------+-------------+
| 9      | ["crispy rice","peppermint","caramel drizzle","shaved chocol | 354         |
|        | ate"]                                                        |             |
+--------+--------------------------------------------------------------+-------------+
| 4      | ["shaved chocolate","white chocolate"]                       | 338         |
+--------+--------------------------------------------------------------+-------------+
| 8      | ["vanilla foam","marshmallow"]                               | 263         |
+--------+--------------------------------------------------------------+-------------+
| 1      | ["peppermint","crispy rice"]                                 | 205         |
+--------+--------------------------------------------------------------+-------------+
| 6      | ["shaved chocolate","dark chocolate","crispy rice","cinnamon | 161         |
|        | ","peppermint"]                                              |             |
+--------+--------------------------------------------------------------+-------------+
| 7      | ["caramel drizzle","crispy rice","marshmallow","vanilla foam | 132         |
|        | ","cinnamon"]                                                |             |
+--------+--------------------------------------------------------------+-------------+
| 3      | ["vanilla foam","peppermint"]                                | 95          |
+--------+--------------------------------------------------------------+-------------+
sqlite> 

Here’s your full SQL file:

Advent of SQL Day 6: Days of Delight

It is day 6 of advent of SQL.

Let’s jump straight into the sql for the day.

DROP TABLE IF EXISTS families;
DROP TABLE IF EXISTS deliveries_assigned;

CREATE TABLE families (
    id INT PRIMARY KEY,
    family_name TEXT
);

CREATE TABLE deliveries_assigned (
    id INT PRIMARY KEY,
    family_id INT,
    gift_date DATE,
    gift_name TEXT
);

INSERT INTO families (id, family_name) VALUES
    (1, 'Isla Martinez'),
    (2, 'Nolan Garcia'),
    (3, 'Yara Chen'),
    (4, 'Tariq Nguyen'),
    (5, 'Mila Hernandez'),
    (6, 'Casey Kim'),
    (7, 'Mateo Hernandez'),
    (8, 'Keiko Petrov'),
    (9, 'Ethan Flores'),
    (10, 'Mateo Nakamura'),
    (11, 'Maya Fernandez'),
    (12, 'Mila Davis'),
    (13, 'Yara Rossi'),
    (14, 'Nolan Phillips'),
    (15, 'Amina Perez');

INSERT INTO deliveries_assigned (id, family_id, gift_date, gift_name) VALUES
    (1, 1, '2025-12-01', 'roasted cashews'),
    (2, 1, '2025-12-02', 'cookie decorating kit'),
    (3, 1, '2025-12-03', 'dark chocolate assortment'),
    (4, 1, '2025-12-04', 'white chocolate candies'),
    (5, 1, '2025-12-05', 'reindeer headband'),
    (6, 1, '2025-12-06', 'holiday brownie bites'),
    (7, 1, '2025-12-07', 'shortbread cookie tin'),
    (8, 1, '2025-12-08', 'chocolate chip cookies'),
    (9, 1, '2025-12-11', 'holiday jam trio'),
    (10, 1, '2025-12-12', 'white chocolate popcorn'),
    (11, 1, '2025-12-14', 'holiday jam trio'),
    (12, 1, '2025-12-15', 'fudge bites'),
    (13, 1, '2025-12-16', 'holiday sticker sheet'),
    (14, 1, '2025-12-18', 'hot cocoa bombs'),
    (15, 1, '2025-12-19', 'honey roasted nuts'),
    (16, 1, '2025-12-20', 'holiday mug'),
    (17, 1, '2025-12-21', 'white chocolate candies'),
    (18, 1, '2025-12-22', 'puzzle book'),
    (19, 1, '2025-12-23', 'snowman plush'),
    (20, 1, '2025-12-24', 'scented hand cream'),
    (21, 1, '2025-12-25', 'vanilla bean wafers'),
    (22, 2, '2025-12-01', 'roasted cashews'),
    (23, 2, '2025-12-02', 'holiday brownie bites'),
    (24, 2, '2025-12-03', 'peppermint bark bites'),
    (25, 2, '2025-12-04', 'holiday jam trio'),
    (26, 2, '2025-12-05', 'festive notepad'),
    (27, 2, '2025-12-06', 'scented pine sachet'),
    (28, 2, '2025-12-07', 'holiday mug'),
    (29, 2, '2025-12-08', 'shortbread cookie tin'),
    (30, 2, '2025-12-09', 'dark chocolate assortment');

So, we have two tables:

Advent of SQL Day 5 - EchoTrack Wrapped

It is day 5 of advent of SQL.

Let’s get rollin. It looks like a good problem. I am excited!

Here’s the SQL to get started.

DROP TABLE IF EXISTS listening_logs;

CREATE TABLE listening_logs (
    id INTEGER PRIMARY KEY,
    user_name TEXT,
    artist TEXT,
    played_at TIMESTAMP,
    content_type TEXT
);

INSERT INTO listening_logs (id, user_name, artist, played_at, content_type) VALUES
    (1, 'Zoe Garcia', 'Arijit Singh', '2025-04-08 00:21:53', 'song'),
    (2, 'Zoe Garcia', 'Huberman Lab', '2025-11-10 19:18:47', 'podcast'),
    (3, 'Zoe Garcia', 'Huberman Lab', '2025-01-20 15:31:02', 'podcast'),
    (4, 'Zoe Garcia', 'Arijit Singh', '2025-01-06 17:33:11', 'song'),
    (5, 'Zoe Garcia', 'Candace', '2025-03-06 14:07:54', 'podcast'),
    (6, 'Zoe Garcia', 'Arijit Singh', '2025-06-05 17:57:59', 'song'),
    (7, 'Zoe Garcia', 'Huberman Lab', '2025-01-01 20:05:22', 'podcast'),
    (8, 'Zoe Garcia', 'Huberman Lab', '2025-11-01 12:04:03', 'podcast'),
    (9, 'Zoe Garcia', 'Arijit Singh', '2025-09-28 12:42:12', 'song'),
    (10, 'Zoe Garcia', 'The Ben Shapiro Show', '2025-09-15 01:05:15', 'podcast'),
    (11, 'Zoe Garcia', 'Arijit Singh', '2025-04-26 05:31:02', 'song'),
    (12, 'Zoe Garcia', 'Arijit Singh', '2025-10-13 17:34:03', 'song'),
    (13, 'Zoe Garcia', 'Mariah Carey', '2025-01-20 11:21:37', 'song'),
    (14, 'Zoe Garcia', 'Arijit Singh', '2025-11-28 03:55:31', 'song'),
    (15, 'Zoe Garcia', 'Arijit Singh', '2025-07-17 05:18:16', 'song'),
    (16, 'Zoe Garcia', 'Arijit Singh', '2025-08-20 02:07:45', 'song'),
    (17, 'Zoe Garcia', 'Kendrick Lamar', '2025-02-16 13:25:27', 'song'),
    (18, 'Zoe Garcia', 'Huberman Lab', '2025-08-13 19:55:00', 'podcast'),
    (19, 'Zoe Garcia', 'Bruno Mars', '2025-09-13 07:09:43', 'song'),
    (20, 'Zoe Garcia', 'Arijit Singh', '2025-04-12 06:30:44', 'song');

Let’s open a SQLite shell and get started.

Week 73

A pretty slow and sluggish week, but some momentum carried in the end. There was a disappointment after a glimmer of excitement when gemini 3 Flash dropped for the experiments that I was running for extraction of documents. It was maybe just the timing, but after this seeing code execution from chats, it was amazing. A good end to the work week.

I have continued to write Advent of SQL for the past 4 days, and brought back the streak of writing SQLog. I was not able to ship some code over the past weekend. But this weekend, I am pumped. I have time sorted out. Would be shipping some improvements in the website. Oh! I actually added snowball and particles based on season on my website (only index and post pages).

Advent of SQL Day 4 WinterFest Volunteers

It is day 4 of advent of SQL.

No fuss, straight to the problem, the elves and humans are getting dumber as the days progress.

Let’s download the SQL inserts for the day.

And load it into a SQLite shell.

DROP TABLE IF EXISTS official_shifts;
DROP TABLE IF EXISTS last_minute_signups;

CREATE TABLE official_shifts (
    id INT PRIMARY KEY,
    volunteer_name TEXT,
    role TEXT,
    shift_time TEXT,
    age_group TEXT,
    code TEXT
);

CREATE TABLE last_minute_signups (
    id INT PRIMARY KEY,
    volunteer_name TEXT,
    assigned_task TEXT,
    time_slot TEXT
);

INSERT INTO official_shifts (id, volunteer_name, role, shift_time, age_group, code) VALUES
    (1, 'Jude Thompson', 'choir_assistant', '12:00 PM', 'senior', NULL),
    (2, 'Mateo Cruz', 'choir_assistant', '12:00 PM', 'senior', NULL),
    (3, 'Olivia Dubois', 'choir_assistant', '2:00 PM', 'teen', 'A1'),
    (4, 'Jeff Bezos', 'choir_assistant', '10:00 AM', 'adult', 'X7'),
    (5, 'Kian Rahimi', 'stage_setup', '12:00 PM', 'adult', 'X7'),
    (6, 'Haruto Sato', 'cocoa_station', '10:00 AM', 'adult', 'X7'),
    (7, 'Uma Singh', 'parking_support', '10:00 AM', 'adult', NULL),
    (8, 'Owen Scott', 'parking_support', '10:00 AM', 'adult', 'X7'),
    (9, 'Adil Rahman', 'stage_setup', '2:00 PM', 'adult', 'A1'),
    (10, 'Aaron Diaz', 'choir_assistant', '2:00 PM', 'senior', 'X7'),
    (11, 'Carter Lewis', 'cocoa_station', '10:00 AM', 'senior', 'B2'),
    (12, 'Anya Pavlov', 'stage_setup', '10:00 AM', 'senior', 'OLD'),
    (13, 'Ethan Brown', 'stage_setup', '2:00 PM', 'adult', 'A1'),
    (14, 'Lucia Fernandez', 'choir_assistant', '12:00 PM', 'senior', 'X7'),
    (15, 'Casey Morgan', 'choir_assistant', '12:00 PM', 'teen', 'OLD');

INSERT INTO last_minute_signups (id, volunteer_name, assigned_task, time_slot) VALUES
    (1, 'Jude Thompson', 'Choir', 'noon'),
    (2, 'Mateo Cruz', 'choir', 'noon'),
    (3, 'Olivia Dubois', 'choir', '2 PM'),
    (4, 'Jeff Bezos', 'choir assistant', '10AM'),
    (5, 'Kian Rahimi', 'stage setup', 'noon'),
    (6, 'Haruto Sato', 'cocoa station', '10AM'),
    (7, 'Uma Singh', 'parking_support', '10AM'),
    (8, 'Owen Scott', 'parking', '10AM'),
    (9, 'Adil Rahman', 'Stage-Setup', '2 PM'),
    (10, 'Aaron Diaz', 'Choir', '2 PM'),
    (11, 'Carter Lewis', 'Cocoa Station', '10AM'),
    (12, 'Anya Pavlov', 'stage_setup', '10AM'),
    (13, 'Olivia Brown', 'stage setup', '2 PM'),
    (14, 'Lena Fischer', 'cocoa station', '2 pm'),
    (15, 'Nolan Murphy', 'parking-support', '10AM');

Once the data is loaded, let’s sneak peak.

Advent of SQL Day 3 - Hotline Messages

This is day 3 from the Advent of SQL

Grab the SQL Statements

Let’s take the insert statements i.e. to create and populate tables and rows into the database. I am using SQLite.

It works without any special shenanigans, as it was intended to used for Postgres, but the table and use case looks very simple, so nothing specific to Postgres used yet! We are good!

SQLog: Advent of SQL Day 2

Here we are on the day 2 of Advent of SQL

As I said in the previous day this is in SQLite so I won’t be doing it in the playground. So here is your SQLite playground :)

SELECT 1;

From now on no setup straight to the problem!

Let’s download the .sql file for today’s problem to see what data we are playing with.

Learning SQLite: Advent of SQL Day 1

I am trying to learn SQLite, I want to understand that database. It’s quite simple yet the whole world uses it for various kinds of things ranging from developers’ toy database to spaceships. What a tiny engineering marvel!

I am happy to see this happening: Advent of SQL

What a better time to learn more. I guess I want to start by exploring all the specificities of the INSERT statement in SQLite after exploring most of the things of the CREATE TABLE statement.