SQL · easy

What is the difference between a measure and a dimension, and why does it matter for a dashboard?

Asked in BI Developer interviews, in the SQL round.

Short answer

A dimension is what you slice by (date, region, product); a measure is what you aggregate (revenue, count, rate). Mixing them up — averaging an already-averaged rate, or summing a ratio — is the classic BI bug. A measure has to aggregate correctly at every grain the user can drill to.

How to answer it

A dimension is what you slice by: date, region, product, customer segment. A measure is what you aggregate: revenue, order count, conversion rate. Dimensions go in GROUP BY; measures go inside aggregate functions.

It matters because a measure has to aggregate correctly at every grain the dashboard can show. SUM(revenue) is right at every grain. A rate is not: the conversion rate for a region is not the average of its cities' rates, it is total conversions over total sessions.

-- correct at any grain: keep numerator and denominator as measures
SELECT region, SUM(conversions)::float / SUM(sessions) AS conversion_rate
FROM daily_funnel
GROUP BY region;

-- wrong: averaging a pre-computed rate weights every city equally
SELECT region, AVG(conversion_rate) FROM city_funnel GROUP BY region;

So in a BI model, store the additive parts and define the ratio as a calculated measure over them. Averages of averages, sums of ratios, and counts of pre-aggregated rows are the three bugs this prevents.

• What they are checking: whether you understand additivity, not whether you can define the two words.
• Common mistake: modelling a rate as a column and letting the tool sum or average it.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Calculate the median order value using SQL.
• Find the top 3 products by revenue within each category.
• Compute month-over-month user retention from an events table.

Practice this for real

• Check your resume against a BI Developer job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card