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.

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.

We have seen how to create TABLE loosely Not adhering to the types. Because if we create a table with column name of type text, and insert a integer, it will happily store it as text. It is very flexible as we saw. So, in such CREATE TABLE statement in SQLite, without the STRICT constraint, the types don’t matter.

If you created a table with column type as XYZ it will accept it, because it really doesn’t see that. It will see the data coming in from the insert statement and store it whatever it thinks is the best one for that piece of data. Look at the below example:

Creating table with Select Query

Sometimes you want to create a new table based on data that already exists,maybe you’re archiving old rows, generating a summary, cloning a table, or transforming data into a new structure. Doing this might require you to write a CREATE TABLE statement with all column definitions, then running one or more INSERT INTO statements to populate it. That’s a lot of work.

Well, SQL is more flexible then you might think, Instead of manually defining columns and inserting data, you write one statement that both builds the table and fills it with the rows returned by your SELECT query. This makes it incredibly useful for backups, and whatever you are doing (hopefully not taking down the prod db).

Creating table if not already exists

The CREATE TABLE has one clause that we can add to create table if it doesn’t exist already. So this comes handy if you already have created a table and instead of throwing an error it simply gracefully handles the query and doesn’t re-create the table.

Let’s first creat a table, as usual, we’ll use the goodol users table.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

Let’s insert some data into the table.

Temporary Table in SQLite

A Temporary table as the name suggests, is a temporary table. It only resisdes on the database until the current session of the database.

In case of sqlite, the temporary table is available in the session until the file reader or session driver closes the connection.

The table is created in a separate database file called temp that is stored in your temporary path.

Create a temporary table

To create the temporary table, simply use the TEMP or TEMPORARY keyword before the TABLE in CREATE TABLE expression. So, CREATE TEMP TABLE or CREATE TEMPORARY TABLE will be the notation to create a temporary table in sqlite.

Collate Column Modifier

Collate is a modifier in SQL that specifies the column how to arrange and compare the values that would be populated in the rows.

So, for a given table, we can specify a few modifiers that would let SQL decide how to handle the values.

Adding Collate Modifier

To add a collate modifier, we can use the COLLATE keyword, followed by the collation name.

There are 3 collations available in SQLite: