🎬 Cinema Screening Slot Analysis

This SQL project analyzes real data from a single-screen cinema to evaluate the effectiveness of individual screening slots in the cinema's programming. The goal is to create specific, data-driven recommendations for improving the programming schedule.

Note: This project uses real company data. The data has been anonymized and all values are expressed as percentage differences relative to a baseline value, not as absolute numbers.

📁 Dataset

The data was being stored in a single Excel table containing the screening date, time, movie name, number of viewers, box office, and several more details that are not important for this case. This raw data was uploaded into a PostgreSQL database on Supabase in its original, unclean form.

🎯 Objectives

• Clean the data and set the correct data types
• Split screenings into screening slots based on day and time
• Calculate audience and box office averages and standard deviation for each slot
• Analyze the results and provide recommendations for cinema management

⚙️ Methodology

The project is structured into five CTE–based steps:

1. Cleaning the data

• Converts the screening date to the DATE type (no cleaning needed)
• Converts the screening time to the TIME type, any value that doesn't match a valid time format is set to NULL
• Converts box office to INT, removing currency symbols and whitespaces
• Extracts a weekday abbreviation from each screening date

WITH screenings_cleaned AS (
    SELECT 
        scr_id::INT,
        TO_CHAR("date"::DATE, 'TMDy') AS day_name,
        "date"::DATE AS date_cln,
        CASE
            WHEN "time" ~ '^\d{1,2}:\d{2}$' THEN "time"::TIME
            ELSE NULL
        END AS time_cln,
        viewers::INT,
        REPLACE(REPLACE(gross_bo, ' Kč', ''), ' ', '')::INT AS boxoffice_cln
    FROM screenings
    WHERE 
        cinema = 'Main'
),

2. Sorting screenings into screening slots

Defines four time-of-day categories and combines them with the weekday name to create individual screening slots. Screenings with missing or invalid time values are excluded from slot assignment and from the rest of the analysis. Only data from 2023 onwards are filtered to keep the data relevant.

screenings_prepared AS (
    SELECT 
        scr_id,
        date_cln,
        day_name || ' ' || 
            CASE
                WHEN time_cln BETWEEN '12:00' AND '15:59' THEN 'Early Aft'
                WHEN time_cln BETWEEN '16:00' AND '18:29' THEN 'Late Aft'
                WHEN time_cln BETWEEN '18:30' AND '20:44' THEN 'Eve'
                WHEN time_cln BETWEEN '20:45' AND '23:59' THEN 'Night'
            ELSE NULL
            END AS scr_slot,
        viewers,
        boxoffice_cln
    FROM screenings_cleaned
    WHERE date_cln > '2023-01-01'
),

3. Calculating overall averages

Calculates the overall average number of viewers and box office. These figures serve as the baseline against which individual slots are later compared.

screenings_averages AS (
    SELECT
        AVG(viewers) AS gen_scr_avg,
        AVG(boxoffice_cln) AS gen_boxoffice_avg
    FROM
        screenings_prepared
),

4. Aggregating by slots

Groups the data by screening slot and calculates the number of screenings, average audience, average box office, and audience standard deviation for each slot. Slots with fewer than 50 screenings are excluded because of a small sample size.

screenings_agg AS (
    SELECT 
        scr_slot,
        COUNT(scr_id) AS scr_count,
        ROUND(AVG(viewers), 1) AS avg_audience,
        ROUND(AVG(boxoffice_cln), 1) AS avg_boxoffice,
        ROUND(STDDEV(viewers), 1) AS stddev_audience
    FROM screenings_prepared
    WHERE 
        scr_slot IS NOT NULL
    GROUP BY 
        scr_slot
    HAVING
        COUNT(scr_id) > 50
)

5. Benchmarking slots

Expresses each slot's average audience and box office as a percentage difference from the overall baselines.

SELECT 
    scr_slot,
    scr_count,
    ROUND((avg_audience / gen_scr_avg) * 100 - 100, 1) AS avg_audience_percent,
    stddev_audience,
    ROUND((avg_boxoffice / gen_boxoffice_avg) * 100 - 100, 1) AS avg_boxoffice_percent
FROM screenings_agg
CROSS JOIN screenings_averages
ORDER BY 
    avg_audience DESC;

🔥 Results and Key Findings

• Saturday performs strongly, improving later in the day, with Saturday evening as the best performing slot overall. It should remain a core of the schedule.
• Sunday shows the opposite pattern – early afternoon performs very well, but performance declines toward the evening, which is the weakest slot overall, likely due to the approaching start of the work week. Shift the whole Sunday schedule to earlier hours and consider reducing the number of Sunday evening screenings.
• Thursday evening and late afternoon both underperform notably.
• Tuesday evening is the weakest slot in terms of box office, though it actually has the highest audience percentage among all work day slots. Its low standard deviation also suggests a loyal audience.

Full results:

scr_slot	scr_count	avg_audience_percent	stddev_audience	avg_boxoffice_percent
Sat Eve	163	14.5	27.3	31.5
Sun Early Aft	171	13.9	28.3	16.8
Sat Late Aft	165	11.0	26.8	20.5
Fri Eve	163	-2.3	24.4	6.7
Sat Early Aft	156	-2.9	25.9	-0.2
Fri Late Aft	162	-3.6	27.0	1.6
Sun Late Aft	165	-7.1	25.3	5.1
Tue Eve	167	-19.4	16.1	-26.5
Thu Late Aft	146	-24.2	24.4	-19.4
Thu Eve	157	-32.4	24.9	-18.6
Sun Eve	158	-37.5	21.6	-25.7