SQL Join for Monthly Spend | Dataford Interview Questions

Write a SQL query to join a user activity table with a billing table to calculate the average monthly spend per active Google Cloud customer.

Treat a customer as active in a month when the activity table contains activity for that customer during that month. Customers with no billing record in an active month should contribute zero spend.

Output

One row per month, with month, active_customer_count, and average_monthly_spend.
Include all months with activity, order chronologically, and round average spend to two decimal places.

Schema

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
customer_id	INT	Google Cloud customer identifier
activity_date	DATE	Date of customer activity
activity_type	VARCHAR(50)	Type of activity recorded

billing

Column	Type	Description
billing_idPK	INT	Unique billing record identifier
customer_id	INT	Google Cloud customer identifier
billing_date	DATE	Date the charge was recorded
amount	NUMERIC(12,2)	Charge amount in USD

Your question is SQL Join for Monthly Spend. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem