Start by defining the date range for the last 7 complete UTC days using a subquery or CTE that calculates the current date in UTC and subtracts 7 days. Then aggregate impressions and clicks separately by date and country, and perform a LEFT JOIN from impressions to clicks to handle cases with no clicks. Finally, compute CTR and RPM using the aggregated metrics, ensuring proper handling of division by zero.
Pro tip: Always clarify the definition of 'last 7 complete UTC days'—it typically means from 00:00:00 UTC 7 days ago up to but not including 00:00:00 UTC today. Also, consider using COALESCE or NULLIF to avoid division by zero errors when calculating CTR and RPM.
Calculate the start and end dates for the last 7 complete UTC days. Use UTC_DATE() or equivalent to get today's date in UTC, then subtract 7 days for the start and exclude today.
Create two subqueries or CTEs: one that sums impressions by date and country, and another that sums clicks by date and country. This ensures correct aggregation before joining.
Perform a LEFT JOIN from the impressions aggregation to the clicks aggregation on date and country, so that impressions with no clicks are retained with zero clicks.
Compute CTR as clicks divided by impressions (using NULLIF to avoid division by zero) and RPM as revenue divided by impressions times 1000. Ensure revenue is included in the impressions aggregation or joined appropriately.
Select the date, country, impressions, clicks, revenue, CTR, and RPM. Order by date and country for readability.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.