SQL Interview Questions: YoY/MoM Growth Calculations and Finding Consecutive Numbers

This article presents a collection of interview‑oriented SQL problems, including table creation, YoY/MoM growth calculations, and methods to find numbers that occur consecutively three or more times.

Table creation and sample data:

-- 创建表并插入数据
CREATE TABLE `saleorder` (
  `order_id` int,
  `order_time` date,
  `order_num` int
);

INSERT INTO `saleorder` VALUES
(1, '2020-04-20', 420),
(2, '2020-04-04', 800),
(3, '2020-03-28', 500),
(4, '2020-03-13', 100),
(5, '2020-02-27', 300),
(6, '2020-01-07', 450),
(7, '2019-04-07', 800),
(8, '2019-03-15', 1200),
(9, '2019-02-17', 200),
(10, '2019-02-07', 600),
(11, '2019-01-13', 300);

YoY (year‑over‑year) compares the current year's value with the same period last year, while MoM (month‑over‑month) compares with the previous month. The formulas are:

YoY growth rate = (current_year_value - last_year_value) / last_year_value * 100%
MoM growth rate = (current_month_value - last_month_value) / last_month_value * 100%

MoM calculation query:

SELECT
    now_month,
    now_num,
    last_num,
    ROUND((now_num - last_num) / last_num, 2) AS ratio
FROM (
    SELECT
        now_month,
        now_num,
        LAG(now_num, 1) OVER (ORDER BY now_month) AS last_num
    FROM (
        SELECT
            SUBSTR(order_time, 1, 7) AS now_month,
            SUM(order_num) AS now_num
        FROM saleorder
        GROUP BY SUBSTR(order_time, 1, 7)
    ) t1
) t2;

YoY calculation using date_add to shift a year forward:

SELECT
    t1.now_month,
    NVL(now_num, 0) AS now_num,
    NVL(last_num, 0) AS last_num,
    NVL(ROUND((now_num - last_num) / last_num, 2), 0) AS ratio
FROM (
    SELECT
        DATE_FORMAT(order_time, 'yyyy-MM') AS now_month,
        SUM(order_num) AS now_num
    FROM saleorder
    GROUP BY DATE_FORMAT(order_time, 'yyyy-MM')
) t1
LEFT JOIN (
    SELECT
        DATE_FORMAT(DATE_ADD(order_time, 365), 'yyyy-MM') AS now_month,
        SUM(order_num) AS last_num
    FROM saleorder
    GROUP BY DATE_FORMAT(DATE_ADD(order_time, 365), 'yyyy-MM')
) t2 ON t1.now_month = t2.now_month;

Finding numbers that appear consecutively at least three times: The problem uses a table Logs(id INT, num VARCHAR). Three solution approaches are demonstrated.

1. Self‑join with DISTINCT:

SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Id = l2.Id - 1
  AND l2.Id = l3.Id - 1
  AND l1.Num = l2.Num
  AND l2.Num = l3.Num;

2. Using row_number() to detect sequences:

SELECT DISTINCT Num "ConsecutiveNums"
FROM (
    SELECT Num,
           (ROW_NUMBER() OVER (ORDER BY id) -
            ROW_NUMBER() OVER (PARTITION BY Num ORDER BY id)) AS series_id
    FROM Logs
) tab
GROUP BY Num, series_id
HAVING COUNT(1) >= 3;

3. Using LEAD() to compare a row with the next two rows:

SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT num,
           LEAD(num, 1) OVER () AS num1,
           LEAD(num, 2) OVER () AS num2
    FROM Logs
) t1
WHERE num = num1 AND num1 = num2;

These queries illustrate common window‑function techniques useful for data‑analysis interview questions.