BookMyShow mid INTERSECTEXCEPTUNION ALLORDER BY

Find every movie_title in this_week_screenings that is a brand new release — meaning it never appeared anywhere in last_week_screenings, across any city or showtime. Each movie should appear exactly once in the result, no matter how many screenings or cities it has this week. Return movie_title, sorted alphabetically.

BookMyShow · Weekly Lineup Changes — practice this real-world SQL scenario live in your browser.

📖 Story BookMyShow · Weekly Lineup Changes

Monday morning at BookMyShow's Programming team. Every Monday, theatres refresh their weekly show schedule, and the team needs a quick lineup-change report: which movies are new releases this week that weren't showing at all last week, and which movies are continuing a second week in theatres. Each screening is logged per showtime and city, so a movie playing on multiple screens or in multiple cities shows up as several rows in the same week's table — the report needs to talk about movie titles, not individual screenings.

🎯 Your Mission

📋 Table Structure

🗂 last_week_screenings

id INTEGER 1

movie_title TEXT Pathaan

city TEXT Chennai

🗂 this_week_screenings

id INTEGER 7

movie_title TEXT Jawan

city TEXT Chennai

⚡ Step-by-Step Walkthrough

Look at both weeks' screenings side by side

query.sql

SELECT 'last_week' AS week, movie_title, city
FROM last_week_screenings
UNION ALL
SELECT 'this_week' AS week, movie_title, city
FROM this_week_screenings
ORDER BY movie_title, week;

💡 Explanation

last_week_screenings and this_week_screenings are two separate tables with the exact same two columns, so they can be stacked directly with UNION ALL — no deduplication here, this step is just about seeing every row from both tables together.
Dunki and Jawan each show up more than once within a single week's table — a movie playing in two cities, or on two different screens, gets one row per showtime, not one row per movie.
Fighter and Merry Christmas only ever appear tagged 'this_week' — there isn't a single 'last_week' row for either of them anywhere in this combined view.
Nothing has been deduplicated or compared across weeks yet — this step only confirms what raw data actually exists in each table before asking which movies are new or continuing.

Warm-up: INTERSECT finds movies playing in both weeks

query.sql

SELECT movie_title FROM last_week_screenings
INTERSECT
SELECT movie_title FROM this_week_screenings
ORDER BY movie_title;

💡 Explanation

INTERSECT keeps only the values that appear in the result of both SELECTs — a movie_title has to exist somewhere in last_week_screenings AND somewhere in this_week_screenings to survive.
Jawan appears twice in last_week_screenings (Chennai and Mumbai) and Dunki appears twice in this_week_screenings (Mumbai and Chennai), but each still shows up exactly once in this result — INTERSECT, like UNION, always returns distinct values, regardless of how many duplicate rows fed into either side.
city was deliberately left out of both SELECTs. If city had been included, Jawan playing in Chennai both weeks would match, but Jawan-in-Mumbai-last-week wouldn't match Jawan-in-Chennai-this-week as the same row — INTERSECT compares whole rows, so only the exact columns selected are what get compared.
Three movies come back: Dunki, Jawan, Salaar — genuinely continuing into a second week, regardless of which city or how many screens they're on.

EXCEPT finds movies this week that never showed last week

query.sql

SELECT movie_title FROM this_week_screenings
EXCEPT
SELECT movie_title FROM last_week_screenings
ORDER BY movie_title;

💡 Explanation

EXCEPT keeps values from the first SELECT that do not appear anywhere in the second SELECT's results — the order matters here, unlike INTERSECT: this_week EXCEPT last_week and last_week EXCEPT this_week ask two different questions.
Dunki, Jawan, and Salaar — the same three movies INTERSECT just found — are correctly excluded here, since all three do appear somewhere in last_week_screenings even if not in the same city.
Fighter and Merry Christmas are the only movie_titles left standing — every this_week_screenings row for either of them has no counterpart anywhere in last_week_screenings, which is exactly what a first-time release looks like.
Animal and Pathaan don't appear in this result at all, even though they were real movies last week — EXCEPT only ever returns rows from the first SELECT, so a movie that dropped out of theatres entirely (in last_week but not this_week) simply has nothing to contribute here; that's a different question, answered by swapping which SELECT comes first.

▶ Practice this live ← Browse all questions