Drill 1 of 3 in Search & ads questions
0

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…