SQL - Counting Distinct IDs for Each Day within the Last 7 Days

In this article, we’ll explore how to count distinct IDs for each day within the last 7 days using SQL. We’ll delve into the technical details of the problem and provide a step-by-step solution.

Understanding the Problem

The problem presents a table with two columns: ID and Date. The ID column represents unique identifiers, while the Date column records dates when these IDs were active. We’re tasked with finding the number of distinct IDs for each day within the last 7 days.

To illustrate this, let’s consider a sample table:

The expected output would be:

This indicates that for each day, we’re counting the distinct IDs present within a 7-day window centered around that date.

Solution Overview

To solve this problem, we’ll employ a combination of techniques from SQL. We’ll use a correlated subquery to compare the current row’s date with dates up to 6 days earlier and later. This will allow us to count distinct IDs for each day within the last 7 days.

Step 1: Creating a Table with Distinct Dates

Before we can start counting distinct IDs, we need to identify all unique dates present in our table. We’ll create a subquery that selects these distinct dates and store it in a temporary result set.

SELECT DISTINCT date AS distinct_dates
FROM table_name;

Step 2: Counting Distinct IDs for Each Day

Now, let’s write the main query that counts distinct IDs for each day within the last 7 days. We’ll use a correlated subquery to compare the current row’s date with dates up to 6 days earlier and later.

SELECT 
    t.date,
    (SELECT COUNT(DISTINCT t2.id)
     FROM table_name t2
     WHERE t2.date >= t.date - INTERVAL '6 day' AND t2.date <= t.date) AS uniques_7day
FROM (
    SELECT DISTINCT date
    FROM table_name
) t;

Explanation of the Query

Let’s break down this query:

1. The first part SELECT DISTINCT date AS distinct_dates FROM table_name selects all unique dates from our table, just like in Step 1.
2. The second part (SELECT COUNT(DISTINCT t2.id) ...) is a correlated subquery that counts the number of distinct IDs for each day within the last 7 days. We’re comparing the current row’s date t.date with dates up to 6 days earlier and later (t.date - INTERVAL '6 day' AND t.date). The inner query selects all rows from our table where these conditions are met.
3. The outer query combines the date column from the first part with the count of distinct IDs from the second part, using an alias uniques_7day.

Example Use Case

Suppose we have a table named sales with columns ID and Date, and we want to find the number of distinct IDs for each day within the last 7 days.

CREATE TABLE sales (
    ID INT PRIMARY KEY,
    Date DATE
);

INSERT INTO sales (ID, Date)
VALUES 
(1, '2018-01-01'),
(2, '2018-01-02'),
(3, '2018-01-03'),
(3, '2018-01-04'),
(2, '2018-01-05'),
(4, '2018-01-06'),
(5, '2018-01-07'),
(2, '2018-01-08');

To execute the query from Step 1 and Step 2:

-- Create a temporary result set with distinct dates
SELECT DISTINCT date AS distinct_dates
FROM sales;

-- Execute the main query to count distinct IDs for each day within the last 7 days
SELECT 
    t.date,
    (SELECT COUNT(DISTINCT t2.id)
     FROM sales t2
     WHERE t2.date >= t.date - INTERVAL '6 day' AND t2.date <= t.date) AS uniques_7day
FROM (
    SELECT DISTINCT date
    FROM sales
) t;

Running this query will produce the expected output:

Conclusion

In this article, we explored how to count distinct IDs for each day within the last 7 days using SQL. We discussed the problem in detail and provided a step-by-step solution that leverages correlated subqueries to compare the current row’s date with dates up to 6 days earlier and later.

By following these steps, you should now be able to write a SQL query that achieves this result for your own tables and data.

Last modified on 2024-06-16