Write SQL for last-7-day metrics without windows

Assume today is 2025-09-01. Use ANSI SQL only and do not use window functions. You may use subqueries, GROUP BY, HAVING, and JOINs. Schema and small sample data follow.

Schema:

• users(user_id INT PRIMARY KEY, signup_date DATE, channel VARCHAR)
• events(user_id INT, event_date DATE, event_type VARCHAR, amount DECIMAL(10,2)) -- event_type in ('session','purchase'); amount is NULL unless event_type='purchase'

Sample data: users user_id | signup_date | channel 1 | 2025-08-29 | Organic 2 | 2025-08-30 | Ads 3 | 2025-08-25 | Referral 4 | 2025-08-31 | Ads 5 | 2025-09-01 | Organic

events user_id | event_date | event_type | amount 1 | 2025-08-30 | session | NULL 1 | 2025-08-31 | purchase | 20.00 2 | 2025-08-31 | session | NULL 2 | 2025-09-01 | purchase | 35.00 3 | 2025-08-26 | session | NULL 3 | 2025-09-01 | session | NULL 4 | 2025-09-01 | session | NULL 5 | 2025-09-01 | session | NULL

Tasks (answer each with a single SQL query, no window functions):

1. Compute DAU for each day in the last 7 days (2025-08-26 to 2025-09-01, inclusive) counting distinct users with a 'session' event. Return columns: event_date, dau.
2. For users who signed up in the last 7 days (2025-08-26 to 2025-09-01), compute the conversion rate by channel = users with ≥1 'purchase' within 7 days of their own signup divided by total signups in that channel. Return: channel, signups, converters, conversion_rate. Ensure you do not double-count users with multiple purchases.
3. Compute day-1 retention for each cohort day D in 2025-08-26..2025-08-31: among users who had a 'session' on day D, what fraction also had a 'session' on day D+1. Return: cohort_date, retained_users, cohort_users, retention_rate. Do this with self-joins or subqueries (no windows).
4. For all users, return their first purchase date (or NULL if none) and lifetime revenue. Return: user_id, first_purchase_date, lifetime_revenue. Use only aggregates and GROUP BY (e.g., MIN with CASE), no windows.
5. Identify, for 2025-08-26..2025-09-01, the top channel by total revenue from purchases made by users in that channel, breaking ties by lexicographically smallest channel name. Return a single row with: channel, total_revenue. Edge cases to handle: multiple sessions per day per user (should not inflate counts), users without purchases, NULL amounts, and users signing up before the 7-day window but active within it.