Write SQL for theme-park revenue and visits

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Capital One.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Capital One during technical interviews.

Question

You are given theme-park ticketing and visits data. Write SQL to answer the following, using the sample schema and tables below. Return both the query and the final numeric answers. Edge cases matter: a user may hold multiple ticket types over time; for (c) count only visits within each holder’s active annual-pass period; include holders with zero qualifying visits in the average.

Schema and sample data:

Table: ticket_types
id | name | price_usd | duration_days
---+--------------+-----------+---------------
1 | single_day | 80 | 1
2 | five_day | 300 | 5
3 | annual_pass | 1000 | 365

Table: ticket_sales -- each row is an order
order_id | user_id | ticket_type_id | quantity | order_date
---------+---------+----------------+----------+------------
101 | 1 | 3 | 1 | 2024-03-15
102 | 2 | 1 | 2 | 2024-06-01
103 | 3 | 2 | 1 | 2024-07-10
104 | 4 | 1 | 1 | 2024-08-20
105 | 5 | 3 | 1 | 2024-08-21

Table: ticket_sales_summary -- pre-aggregated units sold in the last fiscal year
ticket_type_id | units_sold
---------------+------------
1 | 250000
2 | 100000
3 | 10000

Table: annual_pass_periods -- one row per user per pass
user_id | start_date | end_date
--------+------------+----------
1 | 2024-03-15 | 2025-03-14
5 | 2024-08-21 | 2025-08-20

Table: visits -- park entries; a user may have multiple visits
user_id | visit_date
--------+-----------
1 | 2024-06-10
1 | 2024-06-11
1 | 2024-07-01
5 | 2024-09-01
5 | 2024-09-15
2 | 2024-06-15

Tasks:
(a) Compute total revenue and revenue share by ticket type using ticket_sales_summary × ticket_types.
(b) Derive total annual entries implied by sales assuming: single_day yields 1 entry per unit; five_day yields 5 entries per unit; annual_pass yields an unknown average entries per holder, call it X. Express total entries as a function of X.
(c) Using annual_pass_periods and visits, compute the empirical average number of visits per annual-pass holder in their active period (count unique visits per holder within [start_date, end_date], average across holders; include holders with zero visits; exclude visits outside the holder’s active period; if a user bought multiple passes, treat each pass period separately).
(d) Replace X in (b) with your result from (c) and recompute total entries.

PracHub · Accepted Answer

This question evaluates SQL data-manipulation and analytical skills, including joins, aggregations, date-range filtering, handling multiple ticket ownership, and edge cases like zero counts when computing revenue and visit metrics.

Quick Overview

Quick Overview