Pivot daily users and revenue by platform

You are given transaction-level data and need daily aggregates by platform.

Input (Pandas DataFrame)

df with columns:

• file_date (DATE or string parseable to date) — the activity date
• id (INT/STRING) — user id
• sku (STRING) — product identifier
• price (NUMERIC) — revenue for the row (assume already in a single currency)
• channel (STRING)
• customer_segment (STRING)
• platform (STRING) — e.g., 'web', 'mobile'

Example rows:

• 01-01-2024, 123, A, 57, seo, alpha, web
• 01-07-2024, 943, C, 100, tv, alpha, mobile

Task

Compute, for each file_date and platform:

• total_users = number of distinct id
• total_revenue = sum of price

Then reshape the result into a pivoted/wide daily report with one row per day and separate columns per platform:

• file_date
• web_total_users, web_total_revenue
• mobile_total_users, mobile_total_revenue

(If additional platforms exist, include them similarly.)

Pivot daily users and revenue by platform

Table df

column	type	constraints
file_date	DATE
id	INTEGER
sku	VARCHAR
price	DECIMAL(10,2)
channel	VARCHAR
customer_segment	VARCHAR
platform	VARCHAR

Sample data:

channel	customer_segment	file_date	id	platform	price	sku
seo	alpha	2024-01-01	123	web	57	A
tv	alpha	2024-01-07	943	mobile	100	C

Expected output

One row per file_date with distinct user counts and total revenue pivoted into separate columns per platform. If a platform has no activity on a day, its values should be 0.

file_date	mobile_total_revenue	mobile_total_users	web_total_revenue	web_total_users
2024-01-01	0	0	57	1
2024-01-07	100	1	0	0

Hints

1. First aggregate by file_date and platform to compute COUNT(DISTINCT id) and SUM(price).
2. Use conditional aggregation (CASE WHEN platform = 'web' THEN ... END) to pivot into wide columns.
3. Wrap pivoted expressions with COALESCE(...) to replace NULLs with 0.
4. If more platforms exist, add corresponding CASE/COALESCE expressions for each.