Write a SQL query to calculate the rolling 30-day customer acquisition cost (CAC) across different marketing channels for Wise.
Use the supplied marketing spend and customer acquisition records. Include channels and dates represented in either source, treat missing spend or acquisition activity as zero, and return NULL when the rolling acquisition count is zero.
channel, activity_date, rolling_spend, rolling_new_customers, and rolling_cac.channel ascending, with NULL channels first, then activity_date ascending.| Column | Type | Description |
|---|---|---|
| spend_idPK | INT | Unique marketing spend record identifier |
| channel | VARCHAR(50) | Wise marketing channel associated with the spend |
| spend_date | DATE | Date on which the spend was recorded |
| amount | DECIMAL(12,2) | Marketing spend amount in the reporting currency |
| Column | Type | Description |
|---|---|---|
| acquisition_idPK | INT | Unique acquisition event identifier |
| channel | VARCHAR(50) | Wise marketing channel attributed to the acquisition |
| customer_id | INT | Newly acquired Wise customer identifier |
| acquisition_date | DATE | Date on which the customer was acquired |