Defend the number: SQL traps that mislead

A colleague's report says every campaign has a click-through rate of 0%. Their query divided SUM(clicks) by SUM(impressions) and multiplied by 100. Click-through rate (CTR) is total clicks divided by total impressions, as a percentage. For every campaign that has rows in ad_spend, using all weeks, return campaign name, total impressions, total clicks and CTR as a percentage rounded to 2 decimals (for example 3.47). Sort by CTR highest first, then campaign name A to Z.

A colleague's Q3 revenue report has no September row at all. Their filter was order_date BETWEEN '2026-07' AND '2026-09'. For orders with status 'paid' placed from 1 July through 30 September 2026, return month ('YYYY-MM') and paid revenue (quantity × unit_price) rounded to 2 decimals. Sort by month, oldest first.

HR's levelling report says the company has no individual contributors at all. The query was WHERE id NOT IN (SELECT manager_id FROM employees). An individual contributor is an employee whom nobody reports to: no employee has their id as manager_id. Return name and title of every individual contributor, sorted by name A to Z.

Account managers need paid revenue for all 20 customers, including those who never paid for anything. A colleague's version LEFT JOINs orders and then filters WHERE o.status = 'paid', and some customers are missing. Return customer id, name and paid revenue (quantity × unit_price over orders with status 'paid') rounded to 2 decimals, showing 0 for customers without paid orders. Sort by customer id.

Support is planning staff by plan and wants tickets per account. A colleague's figures look too high: after LEFT JOINing support_tickets to accounts they used COUNT(*). For each plan return plan, number of accounts, number of support tickets (all dates, priorities and statuses), and tickets per account rounded to 2 decimals. Every account counts, including accounts that never opened a ticket. Sort by plan A to Z.

A channel report shows revenue per lead that looks too good. The query used AVG(first_order_value), but first_order_value is NULL for leads that never converted. Finance defines revenue per lead as total first_order_value divided by all leads, counting a lead that did not convert as €0. For leads with a campaign (ignore organic leads, whose campaign_id is NULL), return the campaign's channel, the number of leads and revenue per lead rounded to 2 decimals. Sort by revenue per lead highest first, then channel A to Z.

A team summary took each rep's 'average won deal' from the rep dashboard and averaged those numbers per team, so a rep with few wins weighs as much as a rep with many. Finance wants the true average won deal size per team: the mean amount over all deals with stage 'won' belonging to the team's reps. Return team, number of won deals and average won amount rounded to 2 decimals. Sort by team A to Z.

A rep leaderboard shows won amounts far above what finance booked. The query joined deals to activities to count touches, so each won deal was summed once per logged activity. For every rep with at least one deal with stage 'won', return rep name, number of won deals, total won amount and the number of activities logged on those won deals. Sort by total won amount highest first, then rep name A to Z.