7 SQL Metric Patterns from Real-Life Interviews

After going through multiple SQL interviews for data analyst roles, I noticed the same metric questions appearing repeatedly. This article breaks them down into seven patterns and shows how to recognize them quickly in interviews.

Throughout the period between November 2023 and April 2025, I interviewed for multiple data analyst roles and started noticing a clear pattern in SQL interview questions. While the datasets and business contexts changed, the types of metrics interviewers asked about were often the same.

In this article, I group those recurring interview questions into seven metric patterns that appear repeatedly in SQL tests for data analyst positions. Instead of treating each question as a separate problem, I focus on identifying the underlying metric logic that drives most interview tasks.

I build on concepts introduced in earlier articles, including Sales Growth Dataset Exploration – Using The Data Analyst Cheat Sheet on Real Sales Data. However, rather than exploring a specific dataset, I categorize interview questions based on my real interview experience.

To keep the analysis clear and consistent, I follow the same structure for each metric throughout the article. For every pattern, I explain what the metric represents, how it typically appears in SQL interviews, show a representative SQL query, and point to the relevant section of the Data Analyst Cheat Sheet for further review.

The goal is not to memorize queries, but to develop a repeatable way of thinking through SQL interview problems when time is limited. I hope this framework is useful for anyone preparing for a data analyst interview and looking for a clearer way to approach metric-based questions.

I do not claim these solutions are novel. Most SQL interview questions can be solved in multiple ways, and the examples I show use commonly accepted approaches. While more than seven metric patterns exist, I chose these seven because they provide a practical starting point for preparing for SQL interviews.

Overview

I divide the seven metric patterns into two groups: standard metrics and specialty metrics. The standard metrics – KPIs, breakdowns, ratios, and ranks – appear across many reporting and business analysis scenarios and often have multiple variations.

The specialty metrics – cumulative totals, moving averages, and percentage change – are more focused and usually tied to time-based or performance analysis. For each pattern, I provide a general SQL example that is not tied to a specific dataset, so the focus stays on logic rather than data structure.

If you want structured, hands-on practice with these types of metrics, many of the patterns covered here are practiced in the SQL for Data Analysis track, which focuses on the same reporting and analysis scenarios commonly used in data analyst interviews.

Metric 1: KPIs – High-Level Business Performance Metrics

Definition
Key Performance Indicators (KPIs) are basic metrics that show overall business performance. In SQL interviews, KPIs are usually simple aggregate values, such as total sales, total profit, or total number of records, calculated across the entire dataset.

Purpose in interviews
KPI questions are often the first or simplest questions in a SQL interview. Interviewers use them to evaluate whether a candidate can quickly identify what matters in a dataset, choose the correct level of aggregation, and avoid unnecessary complexity.

These questions are often phrased very directly, for example: “How would you compute total sales?”, “What is the total profit?”, or “Give me the overall revenue for this dataset.”

Pattern recognition
KPI questions usually:

• return a single row
• do not require a GROUP BY clause
• rely on aggregate functions such as SUM, COUNT, or AVG

They answer the business question: “How are we doing overall?”

SQL query example

Here is a KPI example taken from a previous article, Data Analyst Cheat Sheet on Real Sales Data:

SELECT
  ROUND(SUM(Sales), 0) AS sales_total,
  ROUND(SUM(Profit), 0) AS profit_total,
  ROUND(SUM(Quantity), 0) AS quantity_total
FROM orders;

This query returns multiple KPIs in a single row by applying different aggregate functions to the same dataset.

Study guide reference
You can review this metric pattern in the Data Analyst Cheat Sheet under Aggregate Functions. For a practical overview of commonly used SQL aggregates, see Aggregate Functions Cheat Sheet.

Metric 2: Breakdowns – Metrics by Time or Category

Definition
A breakdown is a metric that is split into smaller parts to show how it changes over time or how it differs across categories. Instead of one overall number, a breakdown shows multiple rows that explain where or when the metric comes from.

Purpose in interviews
Breakdown questions usually come right after KPI questions. Once you show that you can compute a total, interviewers often ask you to explain it in more detail.

These questions are designed to test whether you can move from a high-level metric to a more granular view and answer questions like “What is driving this number?” or “How does this metric change over time?”

Pattern recognition
Breakdown questions usually:

• include a GROUP BY clause
• return multiple rows
• use the same aggregate functions as KPIs, but grouped by another column

They answer business questions such as: “How does this metric vary by time or by category?”

Type 1: Trend by date

This type of breakdown groups metrics by a time dimension, such as year or month. Interviewers often ask questions like “Show total sales by month” or “How has revenue changed over time?”

SQL query example

SELECT
  EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_year,
  EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_month,
  ROUND(SUM(Sales), 0) AS sales_month,
  ROUND(SUM(Profit), 0) AS profit_month,
  ROUND(SUM(Quantity), 0) AS quantity_month
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;

Type 2: Breakdown by category

This type of breakdown groups metrics by a categorical column, such as product category or region. These questions are often phrased as “Sales by category” or “Profit by product group.”

SQL query example

SELECT
  product_category,
  ROUND(SUM(Sales), 0) AS sales,
  ROUND(SUM(Profit), 0) AS profit,
  ROUND(SUM(Quantity), 0) AS quantity
FROM orders
GROUP BY 1
ORDER BY 1;

Study guide reference
These breakdown patterns are covered across multiple sections of the Data Analyst Cheat Sheet: Aggregate Functions, Date and Time, and ORDER BY. For a practical overview of how grouping and aggregation work together in SQL, see SQL GROUP BY and Aggregate Functions: Overview.

Metric 3: Ratios – Comparing One Metric to Another

Definition
A ratio measures the relationship between two numeric values by dividing one by the other. In SQL interviews, ratios are commonly used to turn raw totals into performance metrics.

Purpose in interviews
Ratio questions test whether you can move beyond absolute numbers and express performance in relative terms. Interviewers often use them to see if you understand how metrics relate to each other, not just how to calculate them.

These questions are commonly phrased as “What is the profit margin?”, “What percentage of total sales does each category represent?”, or “How much of X comes from Y?”

Pattern recognition
Ratio questions usually:

• divide one aggregate by another
• still use aggregate functions such as SUM or COUNT
• sometimes require a subquery or window function to keep the denominator consistent

They answer business questions like: “How efficient is this?” or “How large is this compared to the total?”

Type 1: Overall ratio

This type calculates a single ratio for the entire dataset. It is similar to a KPI, but instead of returning a raw total, it returns a derived performance metric.

SQL query example

SELECT ROUND(SUM(Profit) / SUM(Sales), 2) AS profit_margin
FROM orders;

Type 2: Ratio per category (percent of total)

This type calculates a ratio for each category relative to the overall total. A subquery is used to prevent the denominator from being affected by the GROUP BY clause.

These questions are often phrased as “What percentage of total sales does each category contribute?”

SQL query example

SELECT
  category,
  ROUND(SUM(sales), 0) AS sales_total,
  ROUND((SUM(sales) / (SELECT SUM(sales) FROM orders)) * 100, 0) AS percent_of_total
FROM orders
GROUP BY 1;

Study guide reference

• Overall ratios: Integer Division
• Ratios per category: Computing the Percent of Total Within a Group

Metric 4: Ranks – Ordering Results by Importance

Definition
A rank orders rows based on a metric, usually from highest to lowest or vice versa. In SQL interviews, ranking is used to identify top or bottom performers based on a specific measure.

Purpose in interviews
Rank questions test whether you can transform aggregated results into an ordered list that highlights relative importance. Interviewers often use these questions to see if you understand how to compare entities and present results in a meaningful way.

These questions are commonly phrased as “What are the top-selling products?”, “Which sub-categories perform best?”, or “Rank items by revenue.”

Pattern recognition
Rank questions usually:

• involve sorting aggregated results
• use window functions such as RANK or DENSE_RANK
• return multiple rows with an additional column that represents position

They answer business questions like: “What should we focus on first?” or “Which items matter the most?”

Type 1: Overall ranking

This type ranks all rows across the entire dataset. Using a window function allows you to assign a rank to every row without filtering the result set.

SQL query example

SELECT
  category,
  ROUND(SUM(sales), 0) AS sales_total,
  DENSE_RANK() OVER (ORDER BY SUM(sales) DESC) AS category_rank
FROM orders
GROUP BY 1;

Type 2: Ranking within a category

This type ranks rows within each group separately. It is commonly used when interviewers want to compare performance inside categories, such as sub-categories within each product category.

These questions are often phrased as “What are the top sub-categories within each category?”

SQL query example

SELECT
  category,
  sub_category,
  ROUND(SUM(sales), 0) AS sales_total,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY SUM(sales) DESC) 
    AS sub_category_rank
FROM orders
GROUP BY 1, 2;

Study guide reference
Both ranking patterns rely on window functions covered in the Ranking section of the Data Analyst Cheat Sheet. For a practical walkthrough with examples, see How to Rank Rows in SQL.

Metric 5: Cumulative Metrics – Running Totals Over Time

Definition
A running total is a cumulative sum where each row includes the current value plus all previous values in a defined order, most often time-based. Instead of showing isolated totals, it shows how a metric accumulates over time.

Purpose in interviews
Running total questions are used to test whether you understand window functions and ordered calculations. Interviewers often ask these questions to see if you can combine trend analysis with cumulative logic in a single query.

These questions are commonly phrased as “Show total sales over time with a running total” or “How does revenue accumulate year over year?”

Pattern recognition
Running total questions usually:

• group data by a time dimension (year, month, date)
• use aggregate functions together with window functions
• include an ORDER BY clause inside the window function

They answer business questions like: “How much have we accumulated so far?”

SQL query example

This example calculates yearly sales totals and adds a running total ordered by year:

SELECT
  EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_year,
  ROUND(SUM(sales), 0) AS yearly_sales_total,
  SUM(ROUND(SUM(sales), 0)) OVER (
    ORDER BY EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y')))
  ) AS running_sales_total
FROM orders
GROUP BY 1
ORDER BY 1;

This query produces both the yearly total and the cumulative total in the same result set.

Study guide reference
This metric pattern is covered in the Running Total section of the Data Analyst Cheat Sheet. For a practical explanation with examples, see the article Rolling Total in SQL.

Metric 6: Moving Averages – Smoothing Trends Over Time

Definition
A moving average is a technique used to smooth time-based data by averaging the current value with a fixed number of preceding values. Instead of showing short-term fluctuations, it highlights the underlying trend.

Purpose in interviews
Moving average questions test whether you understand advanced window functions and time-based analysis. Interviewers often use them to evaluate how you handle noisy data and detect patterns such as seasonality or cycles.

These questions are commonly phrased as “Show a rolling average of sales” or “Calculate a 3-month moving average.”

Pattern recognition
Moving average questions usually:

• are based on time-ordered data
• use window functions with a defined frame
• include clauses such as ROWS BETWEEN n PRECEDING AND CURRENT ROW

They answer business questions like: “What is the underlying trend?”

SQL query example

This example calculates monthly sales and applies a 3-month moving average:

SELECT
  EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_year,
  EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_month,
  ROUND(SUM(Sales), 0) AS sales_total,
  ROUND(AVG(SUM(sales)) OVER (
      ORDER BY EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y')))
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
    0) AS moving_average
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;

This query smooths short-term changes by averaging the current month with the two previous months.

Study guide reference
This pattern is covered in the Moving Average section of the Data Analyst Cheat Sheet. For a step-by-step explanation with examples, see Moving Average in SQL.

Metric 7: Percentage Change – Measuring Growth and Decline

Definition
Percentage change measures how much a value has increased or decreased relative to a previous value. It is commonly calculated as:

(new value − old value) / old value × 100

Instead of showing absolute differences, this metric expresses change in relative terms.

Purpose in interviews
Percentage change questions are commonly used to test performance analysis skills. Interviewers often use them to see whether you understand how to compare metrics across time and how to reference previous values correctly.

These questions are often phrased as “What is the year-over-year growth?”, “How did sales change compared to last year?”, or “Calculate the growth rate.”

Pattern recognition
Percentage change questions usually:

• compare a value to a previous row
• rely on window functions such as LAG
• are ordered by a time dimension

They answer business questions like: “Are we growing or declining, and by how much?”

SQL query example

This example calculates year-over-year sales differences by comparing each year to the previous one:

SELECT
  EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_year,
  ROUND(SUM(sales), 0) AS sales_total,
  ROUND(
    LAG(SUM(sales)) OVER (
      ORDER BY EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y')))),
    0) AS sales_prev_year,
  (ROUND(SUM(sales), 0)
   - ROUND(LAG(SUM(sales)) OVER (
       ORDER BY EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y')))
     ), 0)
  ) AS sales_yoy_difference
FROM orders
GROUP BY 1
ORDER BY 1;

This query compares each year’s sales total with the previous year to show how performance changes over time.

Study guide reference
This metric pattern is covered in the Difference Between Two Rows (Delta) section of the Data Analyst Cheat Sheet. For practical examples of growth and comparison metrics, see How to Calculate Revenue Growth in SQL and Year-over-Year Difference in SQL.

Wrap Up

In this article, I broke down seven metric patterns that consistently appear in SQL interviews and grouped them into two categories: standard metrics (KPIs, breakdowns, ratios, and ranks) and specialty metrics (cumulative totals, moving averages, and percentage change). These patterns cover the majority of metric-based questions I encountered during real data analyst interviews.

The goal was not to present new or complex solutions, but to show how interview questions can be simplified by focusing on three core elements: the SQL technique being tested, the underlying metric pattern, and the relevant section of the Data Analyst Cheat Sheet. Approaching problems this way makes SQL interview questions easier to reason about, especially when time is limited.

If you want structured practice with these patterns in a course format, the SQL for Data Analysis track is a good next step, since it covers many of the same reporting and analysis scenarios that show up in data analyst interviews.

In Part 2, I will apply these patterns to real SQL interview questions taken from more than 14 different interviews I completed over the past year, showing how these patterns appear in practice and how to approach them step by step.