Compute ARPDAU/ARPPU by country

Using only the two tables below, write a single SQL query that returns, for each day and country in the last 7 days inclusive (2025-08-26 through 2025-09-01), the following columns: dt, country, dau (distinct users with at least one session that day), payers (distinct users with ≥1 payment that joins to a session that day), revenue_usd (sum of payment amounts joined to sessions that day), arpdau (revenue_usd/dau), and arppu (revenue_usd/payers). Join payments to sessions on the multiple-key (user_id, session_id). Treat dt as DATE(session_start_ts). Assume all timestamps are UTC. Prevent double counting when (a) a session row is duplicated in app_sessions, and (b) a session has multiple payment rows in payments. Only include payments that successfully join to a session; ignore unmatched payments. Provide the query and, in one or two sentences, explain how you avoided double counting. Schema and small sample data: app_sessions(user_id INT, session_id STRING, session_start_ts TIMESTAMP, country STRING)

app_sessions (sample)
+---------+------------+---------------------+---------+
| user_id | session_id | session_start_ts | country |
+---------+------------+---------------------+---------+
| 1 | s1 | 2025-08-31 23:55:00 | US |
| 1 | s2 | 2025-09-01 00:10:00 | US |
| 2 | s3 | 2025-09-01 13:05:00 | CA |
| 2 | s3 | 2025-09-01 13:05:00 | CA | <-- duplicate session row
| 3 | s4 | 2025-08-27 09:00:00 | US |
+---------+------------+---------------------+---------+

payments(user_id INT, session_id STRING, amount_usd DECIMAL(10,2), purchased_at TIMESTAMP)

payments (sample)
+---------+------------+------------+---------------------+
| user_id | session_id | amount_usd | purchased_at |
+---------+------------+------------+---------------------+
| 1 | s2 | 4.99 | 2025-09-01 00:12:00 |
| 1 | s2 | 1.99 | 2025-09-01 00:13:00 | <-- multiple payments same session
| 2 | s3 | 2.99 | 2025-09-01 13:10:00 |
| 3 | s5 | 9.99 | 2025-08-27 09:05:00 | <-- no matching session
+---------+------------+------------+---------------------+

Compute ARPDAU and ARPPU by Country Over a Date Range

Table app_sessions

column	type	constraints
user_id	INT	NOT NULL
session_id	VARCHAR(50)	NOT NULL
session_start_ts	TIMESTAMP	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	session_id	session_start_ts	user_id
US	s1	2025-08-31 23:55:00	1
US	s2	2025-09-01 00:10:00	1
CA	s3	2025-09-01 13:05:00	2
CA	s3	2025-09-01 13:05:00	2
US	s4	2025-08-27 09:00:00	3

Table payments

column	type	constraints
user_id	INT	NOT NULL
session_id	VARCHAR(50)	NOT NULL
amount_usd	DECIMAL(10,2)	NOT NULL
purchased_at	TIMESTAMP	NOT NULL

Sample data:

amount_usd	purchased_at	session_id	user_id
4.99	2025-09-01 00:12:00	s2	1
1.99	2025-09-01 00:13:00	s2	1
2.99	2025-09-01 13:10:00	s3	2
9.99	2025-08-27 09:05:00	s5	3

Expected output

One row per date and country between 2025-08-26 and 2025-09-01 (inclusive) that has at least one session, with DAU, distinct payers whose payments join to sessions, total joined revenue, and ARPDAU/ARPPU computed from the sample data. Days without any sessions are not returned. ARPPU is NULL when there are no payers.

arpdau	arppu	country	dau	dt	payers	revenue_usd
0	NULL	US	1	2025-08-27	0	0
0	NULL	US	1	2025-08-31	0	0
2.99	2.99	CA	1	2025-09-01	1	2.99
6.98	6.98	US	1	2025-09-01	1	6.98

Hints

1. First deduplicate sessions by (user_id, session_id, dt, country) and limit them to dates between 2025-08-26 and 2025-09-01.
2. Aggregate payments per (user_id, session_id), left join to the deduplicated sessions, and use COUNT(DISTINCT ...) for dau and payers to avoid double counting.