The join chain was fine, impression to conversion is a left join and you group by ad_id, standard stuff.
Start by clarifying the schema and assumptions (e.g., date ranges, join keys, revenue definition). Then write a base query that aggregates impressions, conversions, and revenue per ad over the last 30 days, and finally extend it with a window function to rank ads by conversion rate and filter the top 10.
Pro tip: Always handle divide-by-zero when computing conversion rate (e.g., use NULLIF or CASE) and explicitly state that you're using a window function for ranking to avoid aggregation pitfalls.
Ask about table structures, join keys, date column, and how revenue is calculated. Confirm that 'past 30 days' means a rolling window from today.
Write a query that joins ads, impressions, and conversions, filters for the last 30 days, and groups by ad to compute total impressions, total conversions, conversion rate, and total revenue.
Use NULLIF or CASE to avoid division by zero when calculating conversion rate (conversions / impressions).
Extend the query with a window function (e.g., RANK() or DENSE_RANK()) over the conversion rate in descending order.
Wrap the ranked query in a subquery or CTE and filter for rank <= 10, ordering by rank.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.