Posts for: #Advent-of-Sql

Advent of SQL 2025 Day 5: EchoTrack Wrapped

#sqlite  #sql  #advent-of-sql 

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 2025 Day 4: WinterFest Volunteers

#sqlite  #sql  #advent-of-sql 

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 2025 Day 3: Hotline Messages

#sqlite  #sql  #advent-of-sql 

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!

[]

Advent of SQL 2025 Day 2: Snowballs

#sqlite  #sql  #advent-of-sql 

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.

[]

Advent of SQL 2025: Wish List

#sqlite  #sql  #advent-of-sql 

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.

[]