Write SQL for theme-park revenue and visits

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.