Data Analyst Interview
Menu
Browse in your language. All mock interviews, preparation sessions and feedback are in English only.

Audit Missing Customer IDs in SQL: Events Are Not Users

SQL · identity quality

Audit Missing Customer IDs in SQL: Events Are Not Users

Separate received events, identified activity and distinct customers before reporting user growth.

September 26, 2026 · About the author · Synthetic worked example

Which COUNT should you use?

COUNT(*) counts rows. COUNT(customer_id) counts rows with a non-null customer ID. COUNT(DISTINCT customer_id) counts different known customer IDs. Choose the expression that matches your measurement question; these are not interchangeable measures of users.

Imagine a US or UK subscription team receives five checkout events. Two events belong to the same customer, one has no customer ID, and another customer appears twice. The following synthetic dataset contains no real customer information. It measures received events, not completed purchases or daily active users.

Run the example

WITH checkout_events(event_id, customer_id) AS (
  VALUES (1,101),(2,101),(3,NULL),(4,202),(5,202)
)
SELECT COUNT(*) AS event_rows,
       COUNT(customer_id) AS identified_events,
       COUNT(DISTINCT customer_id) AS known_customers,
       COUNT(*) - COUNT(customer_id) AS missing_id_events
FROM checkout_events;

Expected result: 5 event rows, 4 identified events, 2 known customers and 1 missing-ID event. The missing-ID rate is 1/5, or 20%. A dashboard showing only the distinct customer count would conceal that data-quality problem.

The difference between five events and two known customers does not mean three duplicate events. Repeated customers can legitimately perform multiple actions. Deduplicating on customer ID would discard valid activity. Decide whether the source event ID identifies a unique action and check replayed IDs separately.

Should missing IDs become zero?

Do not replace every missing customer ID with zero and then count that value as a customer. That creates one artificial identity shared by all unknown events. It changes a missingness problem into a misleading user count. Keep missing IDs visible, report their rate and investigate the collection or identity process. Nor can you assume each unidentified event belongs to a different person: several could come from one visitor, or from already-known customers.

What happens after a LEFT JOIN?

A customer with no matching order still contributes an output row to a LEFT JOIN. COUNT(*) therefore counts that placeholder row. To count matched orders, count a non-null order key from the order side. If you count a nullable descriptive field, such as a coupon code, you measure orders with that field rather than all orders.

Read the customers with no orders example for the related anti-join problem. Before aggregating a joined table, verify whether one order can have multiple payments or line items. COUNT of the order key can be inflated by those relationships even when the key is never null. The SQL reconciliation exercise shows why grain checks come first.

Can this become a daily-active-user metric?

Only after defining qualifying activity, identity, time zone and the reporting boundary. A checkout-event count is not automatically DAU. A distinct customer count excludes unidentified activity and may count one person under multiple account IDs. Daily unique counts also cannot simply be added to obtain weekly unique users because the same customer may return on several days.

For regional reporting, normalize time boundaries before comparing US and UK results. Use the daily active users time-zone exercise to see why a calendar day is a reporting choice.

How do these counts translate to pandas?

For a customer-ID Series, size counts rows, count() counts nonmissing IDs, and nunique() counts distinct known IDs by default. Keep the same eligible rows and identity rules when comparing the SQL and pandas reports. Including missing values in nunique(dropna=False) does not identify the people behind those records.

In a separate six-order example, four identified orders contain three known customer IDs while two orders remain unidentified. The $340 revenue total still reconciles, but it cannot establish the true customer count. Follow the tested pandas size, count and nunique comparison for the source data, output and missing-identity caveat.

How should you explain the result?

“We received five checkout events, four of which carried a customer ID. Those identified events represent two distinct customer IDs. One event has no ID, so the data does not establish how many additional people it represents. I would report the missing-ID rate alongside the known-customer count.”

This answer distinguishes observations from assumptions. Before publishing, test an empty input, all-null IDs, repeated IDs and joined data. On an empty ungrouped input COUNT returns zero; with GROUP BY, absent groups do not materialize automatically. If the dashboard must show countries with zero events, start from an explicit country list and join the measured results.

Reference: PostgreSQL aggregate functions. This is a worked learning example, not a verified employer interview question or a demonstrated trending keyword.

Leave a Comment

✉ WhatsApp