跳到正文
DEV Community· Emily Woods·· 16 小時前AI 評分53

SQL 面試題:為何三個儀錶板的數字不一致?

SQL Interview Question: Why Do Three Dashboards Disagree?

AI 導讀

文章以六行資料示範,三個儀錶板採用不同的活躍用戶定義,因此分別得出 4、2、1 人。作者將有意義的人類日活躍用戶定義為 UTC 日內至少瀏覽對話或發送訊息、且非內部帳戶的不同非空用戶 ID;例子中的結果為 2。文中以 SQL 展示如何排除登入和內部帳戶、避免重複活動重複計數,並指出遲到事件及報表刷新時點也需事先約定。

正文

One tiny dataset, three defensible answers, and the question to ask before writing another query.

Three dashboards report 4, 2 and 1 active users, leaving an engineer to investigate the definitions.

Someone asks how many people used the product yesterday.

You open one dashboard and find four. Another says two. The third says one, and naturally, all three have very reassuring green checkmarks.

My favorite part of this little disaster is that nobody needs to have written broken SQL.

Maybe one person counted every account that produced an event. Someone else counted customers who actually did something. Finance only wanted paying customers. Everybody answered a reasonable question, then gave their answer the same label.

That is the interview exercise we’re going to work through. It is an original practice scenario, and you can check the entire answer with six rows.

Please don’t debug the chart yet

Imagine a small messaging product. We want a daily count for November 19, using UTC. Accounts are already classified as paid, free, or internal at the time of each event.

In this example, internal accounts are test automation. A view means someone opened a conversation, a send means they sent a message, and login only means authentication succeeded.

Six source events include a repeated user, an internal account, a login-only account and an event at the next midnight.

Before reading further, work out which user IDs belong in yesterday’s count. You can probably see why the question needs another sentence.

If any recorded activity qualifies, the answer is four, for users 1, 2, 3 and 9.

If activity means a view or a send from a non-internal account, the answer is two, for users 1 and 2.

If we additionally require a paid account, the answer is one, for user 1.

User 4 belongs to November 20. User 1 does not become two people because they pressed two buttons.

All three definitions can be useful. Trouble begins when a dashboard simply calls all of them active users.

Three counting rules select four, two and one distinct users from the same day's events.

For a separate exercise with an explicit definition, try the Amazon’s daily active user question. Write the first and last included dates before you write SQL, then check why duplicate activity rows must not increase the user count.

The Meta tagged video call percentage question adds a useful twist. Decide who belongs in the denominator, then remember that receiving a call can qualify someone too. These are the company labels on the practice pages like our three-dashboard example is separate.

Give the number a name that admits what it means

For our product, let’s choose meaningful human activity. A person must view a conversation or send a message. Login alone does not qualify, and internal accounts are excluded.

I prefer a slightly awkward metric name over a beautifully ambiguous one, so this becomes meaningful_human_dau.

That name still needs a definition beside it. Ours counts distinct non-null user IDs with at least one qualifying event during the UTC calendar day. It includes the opening midnight and excludes the following midnight.

For this fixture, a single user ID represents one person, account classification is known, and the events are already consistently recorded. Those are assumptions to state, not universal truths about every application.

A metric card defines the counting unit, qualifying activity, exclusions, UTC window and late-arrival policy.

The half open time window is particularly handy. An event at exactly midnight belongs to the new day rather than sneaking into both daily reports.

And if a teammate asks whether a login should count, that is a product conversation worth having. It is not an invitation to quietly change the WHERE clause and hope nobody notices.

Now the SQL has something precise to do

Here is the complete example. The timestamps are fixed-format UTC text for this small, runnable fixture, so they sort chronologically. In an application, use your database’s appropriate timestamp types and explicit timezone handling.

WITH events(user_id, action, account, event_time) AS (
  VALUES
    (1, 'view',  'paid',     '2026-11-19 09:00:00'),
    (1, 'send',  'paid',     '2026-11-19 09:05:00'),
    (2, 'view',  'free',     '2026-11-19 10:00:00'),
    (3, 'login', 'paid',     '2026-11-19 11:00:00'),
    (9, 'send',  'internal', '2026-11-19 12:00:00'),
    (4, 'view',  'paid',     '2026-11-20 00:00:00')
),
day_events AS (
  SELECT *
  FROM events
  WHERE event_time >= '2026-11-19 00:00:00'
    AND event_time <  '2026-11-20 00:00:00'
)
SELECT
  COUNT(DISTINCT user_id) AS any_activity_users,
  COUNT(DISTINCT CASE
    WHEN account <> 'internal'
      AND action IN ('view', 'send')
    THEN user_id
  END) AS meaningful_human_dau,
  COUNT(DISTINCT CASE
    WHEN account = 'paid'
      AND action IN ('view', 'send')
    THEN user_id
  END) AS paying_meaningful_dau
FROM day_events;

The result is 4, 2, 1. This fixture and query were checked in SQLite.

Each CASE produces a user ID only when its rule qualifies. Otherwise it produces NULL, which COUNT ignores. DISTINCT prevents repeat events from multiplying people.

Notice what the query cannot decide for us. It cannot determine whether login demonstrates engagement, whether a test account should count as a customer, or whether the business reports in UTC. We supplied those choices.

An AI assistant can write all three expressions quickly, but a vague request for active users still leaves it choosing among meanings. A query that runs successfully does not settle that disagreement.

This is where the semantic layer stops sounding fancy

A semantic layer gives shared business definitions a reusable home. For example, dbt’s documentation describes defining metrics centrally so downstream tools use consistent definitions.

That is useful when several dashboards, notebooks, or AI tools need the same number. It does not decide which definition your business should adopt, and a centrally maintained mistake is still a mistake.

For a small project, begin with a reviewed query or view, a written definition, and examples whose answers everyone agrees on. You can introduce more infrastructure when the number of consumers justifies it.

Also, keep legitimate alternatives. Finance can still have paying_meaningful_dau. The goal is to make differences visible, not force every team to answer a different business question with the same number.

Make one row argue with your definition

This is the part I’d bring into an interview discussion after explaining the result. Change the data and predict the outcome before running anything.

  • Duplicate user 1’s send event. Our meaningful human count should remain two.

  • Add another internal test event. It should still remain two.

  • Add a qualifying event exactly at the next midnight. It belongs to the next day.

  • Deliver a late view event for user 3 that actually occurred on November 19. Under an event-time report that accepts revisions, yesterday’s count becomes three.

Four deliberate changes test duplicates, internal accounts, the midnight boundary and late-arriving activity.

That last case deserves a little attention. Event time tells us when something happened; arrival time tells us when we learned about it. A report can be correct when first computed and change after more data arrives.

Our proposed policy is to revise the event-date count for accepted late events and expose when the report was refreshed. A real team also needs to agree how long revisions remain open. Don’t let two dashboards use different refresh cutoffs while claiming they show the same snapshot.

For larger systems, another good follow-up is whether a user can appear under several IDs or change account status during the day. Our six rows avoid that complexity deliberately. Once it exists, identity resolution and the timing of account classification become part of the metric definition too.

Bring the definition into the answer

A useful interview response can explain that the count is two because users 1 and 2 performed qualifying human activity in the agreed UTC window. User 3 only logged in, user 9 is internal, and user 4 falls outside the day.

That explanation is much easier to defend than a query followed by a hopeful shrug.

I like this exercise because the SQL is small enough that you cannot hide behind it. You have to connect the number to the behavior it represents.

Next time two dashboards disagree, ask each owner which people their number includes. You might still find a broken query, but at least you’ll be debugging the same question.

~ Emily

來源:DEV Community · dev.to