Interview Question Bank · COUNT(col) vs COUNT(*) · AVG skips NULL
1. Average clicks when tracking had gaps
EasySearch & adsQuestion #253
Concept: COUNT(col) vs COUNT(*) · AVG skips NULLshow
Aggregates treat NULL as unknown and skip it: COUNT(col), SUM, AVG, MIN and MAX ignore NULLs, while COUNT(*) counts rows. So AVG(col) is already the average over the known values. Turning NULL into 0 with COALESCE changes the question, because it claims the value was zero and drags the average down. And 0 is a real value, so don't filter it away either.
Example (different data):
SELECT sensor_id, COUNT(*) AS readings_expected, COUNT(temp_c) AS readings_received, ROUND(AVG(temp_c), 1) AS avg_temp FROM sensor_readings GROUP BY sensor_id;
Your task
Findwell, a search engine that sells text ads, lost click tracking on some days in early March 2025. Those days have clicks NULL; a day with clicks 0 was tracked and nobody clicked.
For each ad, return ad_id, days_logged (all of its rows), days_tracked (days where clicks was recorded, 0 included) and avg_clicks (average daily clicks over tracked days only, rounded to 2 decimals). Sort by ad_id.
Starting Postgres in your browser…
Checking your access…