Simplified
Simplified

Who watched what

YOU
Lab 8

Who watched what

Write each query here, and run it on a real Postgres database in your browser.

Save who watched which video in a join table, and read it from both sides.

Scenario

Simplified saves each video a learner finishes watching. You save new ones, then answer who watched what.

Time
About 12 min
Steps
6
Where
Your browser

What this lab tests

  • 4.5 Many-to-Many: When neither table can hold the key, A third table for the pairs
  • 4.4 Reading Across Tables: Reading two tables side by side, Filtering the joined rows
  • 4.1 Adding Rows: Three rows in one go
  • 3.7 Counting: One number from millions of rows, One count for each group

Simplified keeps learners in users, videos in videos, and one row in watches each time a learner finishes a video.

usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11videosidtitlechapterseconds1Servers11402Memory and disk1953Queries and results11204The primary key21105Generated ids2956The schema2807Text in quotes31508The empty result3100watchesuser_idvideo_id11122143

Can you save a new pair, and find who watched what?

Each row of watches holds two ids: the learner's user_id, and the video_id of what they watched.

PostgreSQLStarting in your browser…
1

Step 1: Eli watches two videos

Eli, whose id is 5, finishes two videos.

They are Memory and disk, whose id is 2, and Queries and results, whose id is 3.

Save both pairs with one INSERT.

TestsA third table for the pairs4.5 Many-to-ManyThree rows in one go4.1 Adding Rows
SQLYour query
Connecting…
1
2
3
⌘ / Ctrl Enter to run
2

Step 2: Save Fay's video in the right table

Fay, whose id is 6, finishes Servers, whose id is 1.

An assistant tried to save that in Fay's row of users.

Predict what Postgres does, run it, then save the pair where it belongs.

TestsWhen neither table can hold the key4.5 Many-to-ManyA third table for the pairs4.5 Many-to-Many
Pass step 1 to open this one.
3

Step 3: What Ada has watched

The team checks what Ada, whose id is 1, has watched.

Send back the title of every video Ada has watched.

TestsA third table for the pairs4.5 Many-to-ManyReading two tables side by side4.4 Reading Across TablesFiltering the joined rows4.4 Reading Across Tables
Pass step 2 to open this one.
4

Step 4: Who has watched Servers?

Next, the team checks who has watched Servers, whose id is 1.

Send back the name of every learner who has watched it.

TestsReading two tables side by side4.4 Reading Across TablesFiltering the joined rows4.4 Reading Across Tables
Pass step 3 to open this one.
5

Step 5: Count one video's learners

The team asks how many learners have watched Queries and results.

Send back that one number, finding that row of videos by its title.

TestsReading two tables side by side4.4 Reading Across TablesFiltering the joined rows4.4 Reading Across TablesOne number from millions of rows3.7 Counting
Pass step 4 to open this one.
6

Step 6: Learners for each video

Last, the team wants to see how many learners have watched each video.

Send back each video_id, with how many rows of watches it has.

TestsA third table for the pairs4.5 Many-to-ManyOne count for each group3.7 Counting
Pass step 5 to open this one.

column "video_id" of relation "users" does not exist

users has no video_id column.

A pair goes in watches, as one new row.

missing FROM-clause entry for table "users"

The query names users, but never reads it.

Add it after JOIN: FROM watches JOIN users.

insert or update on table "watches" violates foreign key constraint "watches_video_id_fkey"

The video_id matches no id in videos, so Postgres refuses the row.

videos holds ids 1 to 8.

Finish the lab

Each step passes once its query sends back the right rows, or makes the right change. Then mark the lab complete to unlock the next lesson.

0 of 6 steps done
Assistant

Highlight lesson text and click Ask AI, or type a question below.