Tracking New and Repeat Customers Using DENSE_RANK() and COUNT OVER

LeetCode Hard SQL

When analyzing transactional data, a very common problem is to understand customer behavior over time — specifically distinguishing new customers from repeat customers on any given day. This is a scenario that often shows up in real-world SQL analytics and LeetCode-style interview questions.

Get Rajesh kaki’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

In this post, we’ll look at how to solve this elegantly using window functions.

Problem Statement

Given an orders table containing:

👉 Goal:

For each order_date, determine:

• How many customers placed their first-ever order on that date (new_cus)
• How many customers placed a repeat order on that date (repeat_cnt)

🔹 SQL Query

WITH cte AS (
  SELECT  
    order_date,
    DENSE_RANK() OVER (ORDER BY order_date) AS rnk,
    COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) AS cus_cnt
  FROM orders
)
SELECT 
  order_date,
  SUM(CASE WHEN cus_cnt = 1 THEN 1 ELSE 0 END) AS new_cus,
  SUM(CASE WHEN cus_cnt > 1 THEN 1 ELSE 0 END) AS repeat_cnt
FROM cte
GROUP BY order_date
ORDER BY order_date;

Explanation

Step 1: Compute customer order history

• The expression:
• sql
• CopyEdit
• COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) AS cus_cnt
• gives, for every order, how many times that customer has ordered up to and including that date.

Step 2: Classify customers

• If cus_cnt = 1: this is the customer's first-ever order ⇒ new customer.
• If cus_cnt > 1: this customer has ordered before ⇒ repeat customer.

Step 3: Aggregate by date

• For each order_date, we use:
• sql
• CopyEdit
• SUM(CASE WHEN cus_cnt = 1 THEN 1 ELSE 0 END) AS new_cus SUM(CASE WHEN cus_cnt > 1 THEN 1 ELSE 0 END) AS repeat_cnt
• to count new and repeat customers separately.

Step 4: GROUP BY order_date

• Ensures we get one row per date with these counts.

🔹 Functions and Techniques Used

🔧 DENSE_RANK()

• Assigns a rank to each distinct order_date in chronological order.
• Helps track the sequence of dates (optional in this case but useful for ordering).

COUNT(*) OVER (PARTITION BY ... ORDER BY ...)

• A window function that gives a running total of orders for each customer up to that point.
• Key to identifying if a customer is “new” or “repeat”.

SUM(CASE WHEN ...)

• A pattern for conditional counting during aggregation.

GROUP BY

• Groups results by order_date so we can report per day.

Why this matters

This query structure is:

• Highly efficient: No need for subqueries or self-joins.
• Scalable: Works even for large datasets.
• Clean and readable: Window functions keep logic declarative and intuitive.

This pattern is useful for many common reporting needs:

• First-time vs. returning user analysis
• Daily cohort tracking
• Customer retention studies

Key takeaway:
Window functions like COUNT OVER combined with conditional aggregation (SUM(CASE WHEN ...)) provide a simple yet powerful way to analyze customer behavior over time — an essential SQL skill for analytics professionals and interview preparation.