Build a cohort dashboard with Streamlit and SQL

You are given three tables and asked to build a minimal Streamlit app that helps a PM explore weekly cohort retention and ARPU by country. Assume SQL dialect is BigQuery Standard SQL and 'today' is 2025-09-01 UTC.

Schema and tiny sample data:

users
+---------+---------------------+---------+
| user_id | signup_ts | country |
+---------+---------------------+---------+
| 1 | 2025-07-25 10:05:00 | US |
| 2 | 2025-07-26 13:10:00 | IN |
| 3 | 2025-08-01 09:00:00 | US |
| 4 | 2025-08-15 20:30:00 | DE |

events
+----------+---------+---------------------+------------+
| event_id | user_id | event_ts | event_type |
+----------+---------+---------------------+------------+
| e1 | 1 | 2025-08-01 12:00:00 | open |
| e2 | 1 | 2025-08-02 09:05:00 | click |
| e3 | 2 | 2025-08-01 08:00:00 | open |
| e4 | 3 | 2025-08-08 14:00:00 | open |
| e5 | 3 | 2025-08-15 15:00:00 | click |
| e6 | 4 | 2025-08-16 16:00:00 | open |

purchases
+---------+---------+---------------------+--------+
| order_id| user_id | order_ts | amount |
+---------+---------+---------------------+--------+
| o1 | 1 | 2025-08-03 10:00:00 | 19.99 |
| o2 | 3 | 2025-08-16 18:00:00 | 9.99 |

Tasks:

1. Write a single BigQuery query (must use window functions with PARTITION BY) that outputs a cohort-retention heatmap table: columns (signup_week, week_index, country, cohort_size, retained_users, retention_rate) where signup_week = DATE_TRUNC(DATE(signup_ts), WEEK(MONDAY)) and week_index = DATE_DIFF(DATE_TRUNC(DATE(event_ts), WEEK(MONDAY)), signup_week, WEEK). A user is 'retained' in week k if they have ≥1 event that week. Only include weeks where signup_week <= 2025-08-25 and event_ts <= 2025-09-01. Explain how your query avoids double-counting users across weeks and handles users with late events.
2. Extend the SQL to compute weekly ARPU by cohort-country: ARPU = SUM(amount) over users in the cohort with orders whose order_ts falls in the corresponding activity week, divided by cohort_size. Ensure users without orders contribute zero in ARPU but still count in cohort_size. Use appropriate JOINs and window frames.
3. Implement a Streamlit app that:

• lets the user filter by country (multi-select) and choose metric = {Retention, ARPU};
• displays a heatmap (for retention) or a line chart by week_index (for ARPU) using Plotly or Altair;
• treats missing future weeks as NA (not zero) and masks cohorts that are <50 users; and
• allows a UTC offset selector to re-bucket events (briefly describe how you would precompute or on-the-fly adjust dates to honor timezone without duplicating users across weeks).

4. Briefly describe two data-quality checks you would code into the app (e.g., cohort-size monotonicity vs. observed weeks, guard against clock-skew producing negative week_index). Answer with the SQL and the Streamlit code structure (high level) plus the timezone strategy.

Weekly Cohort Retention and ARPU by Country (PostgreSQL)

You are given three tables in PostgreSQL: users, events, and purchases. Write a single PostgreSQL query that produces a weekly cohort-retention and ARPU table by country.

Definitions:

• signup_week = DATE_TRUNC('week', signup_ts)::date
• event_week = DATE_TRUNC('week', event_ts)::date
• order_week = DATE_TRUNC('week', order_ts)::date
• week_index = (event_week_or_order_week - signup_week) / 7
• A user belongs to the cohort identified by (signup_week, country).
• A user is "retained" in week k if they have at least one event in that week.
• ARPU (Average Revenue Per User) for a cohort-week is: total purchase amount from orders whose order_ts falls in that week, made by users in the cohort, divided by the cohort_size (so users without orders contribute zero revenue but still count in the denominator).

Requirements:

1. The query must output one row per (signup_week, week_index, country) with columns: signup_week, week_index, country, cohort_size, retained_users, retention_rate, arpu
2. Only include rows where signup_week <= DATE '2025-08-25' and event_ts <= TIMESTAMP '2025-09-01 00:00:00'.
3. Use window functions with PARTITION BY to derive cohort_size.
4. Ensure each user is counted at most once per (signup_week, week_index, country) when computing retained_users (no double-counting across multiple events in the same week), and that users can still be retained in later weeks if they return.

Use the schema and sample data below and write a query that would work correctly on large datasets with the same structure.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
signup_ts	TIMESTAMP	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	signup_ts	user_id
US	2025-07-25 10:05:00	1
IN	2025-07-26 13:10:00	2
US	2025-08-01 09:00:00	3
DE	2025-08-15 20:30:00	4

Table events

column	type	constraints
event_id	VARCHAR(20)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
event_ts	TIMESTAMP	NOT NULL
event_type	VARCHAR(20)	NOT NULL

Sample data:

event_id	event_ts	event_type	user_id
e1	2025-08-01 12:00:00	open	1
e2	2025-08-02 09:05:00	click	1
e3	2025-08-01 08:00:00	open	2
e4	2025-08-08 14:00:00	open	3
e5	2025-08-15 15:00:00	click	3
e6	2025-08-16 16:00:00	open	4

Table purchases

column	type	constraints
order_id	VARCHAR(20)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_ts	TIMESTAMP	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	order_id	order_ts	user_id
19.99	o1	2025-08-03 10:00:00	1
9.99	o2	2025-08-16 18:00:00	3

Expected output

One row per (signup_week, week_index, country) containing the cohort size, the number of retained users in that week, the retention rate, and ARPU (revenue per original cohort user) for that cohort-week. Based on the sample data, the output would be:

arpu	cohort_size	country	retained_users	retention_rate	signup_week	week_index
0	1	IN	1	1	2025-07-21	1
19.99	1	US	1	1	2025-07-21	1
0	1	US	1	1	2025-07-28	1
9.99	1	US	1	1	2025-07-28	2
0	1	DE	1	1	2025-08-11	0

Hints

1. Use date_trunc('week', timestamp)::date for Monday-start weeks in PostgreSQL.
2. Compute week_index from the day difference divided by 7.