Write the SELECT portion of a daily load job into an aggregate table, combining the new daily data with the prior snapshot of the aggregate table (full outer join).
Asked in the ETL 1 stage. The query should produce the next daily snapshot from the source events and the previous aggregate snapshot.
account_id, snapshot_date, total_events, and total_revenue.2025-02-15 as the output date, and order by account_id ascending.| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| account_id | INT | Account associated with the event |
| event_date | DATE | Date on which the event occurred |
| event_count | INT | Number of events represented by the row |
| revenue | NUMERIC(12,2) | Revenue attributed to the event row |
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Account represented in the snapshot |
| snapshot_datePK | DATE | Date of the aggregate snapshot |
| total_events | INT | Cumulative event total through the snapshot date |
| total_revenue | NUMERIC(12,2) | Cumulative revenue through the snapshot date |