Metric Value Quarter Complement

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L4

Asked in

Airbnb

Problem

List every entry in employee_metrics where the integer part of its metric value plus the number in its fiscal quarter label (Q1 through Q4) comes to exactly 5. The budget allocation formula under test reads those entries in entry id order, lowest first.

Two metrics that accidentally match.

Tables

employee_metrics

Columns: metric_id INTEGER, department TEXT, metric_name TEXT, metric_value REAL, fiscal_quarter TEXT, fiscal_year INTEGER

Expected output

Worked solution and explanation

This is two type conversions dressed up as a budget formula. metric_value is a float and fiscal_quarter is a label like 'Q3', and the formula only means something once both are whole numbers. Everyone spots the 'Q'. What separates candidates is reading 'integer part' as truncation. Reach for ROUND and 1.5 becomes 2 and 3.7 becomes 4, so entries 2355 and 2929 fall out of the answer, while values whose rounding lands one higher slip in.

Getting both sides to whole numbers

Step 1: Turn the quarter label into a number

SUBSTR(fiscal_quarter, 2) gives you the text '3', not the number 3. CAST(... AS INTEGER) is what makes it a number. That matters because the comparison puts it against arithmetic: text never equals a number, so leaving the cast off returns nothing at all.

Step 2: Truncate the metric value

CAST(metric_value AS INTEGER) drops the decimals toward zero, so 2.9 contributes 2. That is what 'integer part' means; ROUND answers a different question, and the preview row 2355 (1.5 in Q4) only qualifies under truncation.

Step 3: Compare the quarter to what is left of 5

Saying the quarter number equals 5 - CAST(metric_value AS INTEGER) is the same formula as 'the sum is 5', with each side one clean whole number. Rows with a missing metric_value drop out on their own: the right side is NULL, and NULL never equals anything.

Step 4: Sort by metric_id

The ids are not sequential (cloned entries sit above 10,000,000), so you cannot assume any order; the sort is what produces it.

SELECT
  metric_id,
  department,
  metric_name,
  metric_value,
  fiscal_quarter,
  fiscal_year
FROM employee_metrics
WHERE CAST(SUBSTR(fiscal_quarter, 2) AS INTEGER) = 5 - CAST(metric_value AS INTEGER)
ORDER BY metric_id

Rounding

ROUND(metric_value) turns 1.5 into 2 and 3.7 into 4. Paired with Q4 and Q2, both rows come out to 6 and vanish from the answer.

Truncating

CAST(metric_value AS INTEGER) keeps 1 and 3. Paired with Q4 and Q2, both rows land on exactly 5, which matches the expected 2355 and 2929.

Common follow-up questions

• What changes if `fiscal_quarter` starts arriving as 'FY26-Q3'? (Tests parsing by position versus by pattern.)
• What if `metric_value` can be negative, say -1.5? (Truncation toward zero gives -1 and a floor gives -2: which one does the formula want?)
• How would you model the quarter so this filter could use an index? (Tests storing the number as a column instead of re-deriving it in every query.)