Google SQL Interview Question: Detect Users With Increasing Value Trend

In this post, we’ll solve a real Google SQL interview question that frequently appears in FAANG-style interviews:

Identify users whose activity value keeps strictly increasing over time.

This problem tests your understanding of:

• Window functions (LAG)
• Partitioning and ordering
• Handling edge cases (first record, NULLs)
• Aggregations with HAVING

Let’s break it down step by step.

---

📌 Problem Statement

You are given a table called user_metrics with the following columns:

Column Name	Description
user_id	Unique user identifier
activity_date	Date of activity
value	Activity value

Goal

Find all users whose value strictly increases over time when sorted by activity_date.

---

📊 Sample Intuition

• User 1: 10 → 20 → 30 ✅ Increasing → Include
• User 2: 15 → 12 → 18 ❌ Decrease happened → Exclude
• User 3: 5 → 8 ✅ Increasing → Include
• User 4: 50 ❌ Only one record → Exclude

---

🧠 Strategy

To solve this problem, we’ll follow these steps:

1. Use LAG() to compare the current value with the previous value for each user.
2. Create a flag that marks where the increasing trend breaks.
3. Aggregate at the user level to keep only users with:

• No breaks in trend
• More than one activity record

---

🧩 Step 1: Get Previous Value Using LAG()

We use a window function to fetch the previous value for each user:

WITH prev_values AS (
    SELECT
        user_id,
        activity_date,
        value,
        LAG(value) OVER (
            PARTITION BY user_id
            ORDER BY activity_date
        ) AS prev_value
    FROM user_metrics
)

This gives us the current value and the previous value for comparison.

---

🚦 Step 2: Create a Flag to Detect Trend Breaks

Now we create a flag:

• 0 → Trend is valid (increasing or first record)
• 1 → Trend is broken (value decreases)

, flagged_rows AS (
    SELECT
        user_id,
        activity_date,
        value,
        CASE
            WHEN prev_value IS NULL THEN 0
            WHEN value > prev_value THEN 0
            ELSE 1
        END AS flag
    FROM prev_values
)

📌 Important Edge Case
The first record for each user has NULL as prev_value.
We treat it as valid (flag = 0) so it can be part of the increasing streak.

---

🧮 Step 3: Aggregate to Find Valid Users

Finally, we group by user_id and apply filters:

• SUM(flag) = 0 → No decreases
• COUNT(*) > 1 → At least two records

SELECT
    user_id
FROM flagged_rows
GROUP BY user_id
HAVING
    SUM(flag) = 0
    AND COUNT(*) > 1;

---

✅ Final Output

This query correctly returns users whose activity values strictly increase over time, excluding:

• Users with any decrease
• Users with only one activity record

For the sample data, the result would be:

user_id
-------
1
3

---

🎯 Why This Question Matters in Interviews

This problem is a favorite in Google / Meta / Amazon SQL rounds because it tests:

• Analytical thinking
• Window function mastery
• Handling NULLs correctly
• Proper use of HAVING vs WHERE

If you can explain this solution clearly in an interview, you’re operating at a strong FAANG-ready level.

---