SQL for Theater Revenue Insights | Dataford Interview Questions

Write a SQL query to calculate daily concession revenue, ticket sales, and average transaction value by theater location at Regal.

Use completed transactions only. Treat ticket sales as the total amount from ticket transactions, and average transaction value as the average amount across completed transactions.

Output

One row per theater location and transaction date.
Columns: location, transaction_date, concession_revenue, ticket_sales, and average_transaction_value.
Include only dates with completed transactions, order by location ascending and transaction_date ascending.

Schema

theaters

Column	Type	Description
theater_idPK	INT	Unique Regal theater identifier
location	VARCHAR(100)	Theater location name

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
theater_id	INT	Theater associated with the transaction
transaction_at	TIMESTAMP	Timestamp when the transaction occurred
transaction_type	VARCHAR(20)	Transaction category, either ticket or concession
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status

Your question is SQL for Theater Revenue Insights. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem