Getting Data Within Range Date, Broken Down by Each Day, with a Single Query in SQL

As a data-driven application developer, understanding how to extract and manipulate data from databases is crucial. In this article, we’ll explore how to get data within a specific date range, broken down by each day, using a single SQL query.

Understanding the Problem

We have a table that logs session activities from users, with fields such as id, name, category, total_steps, created_at, training_id, and user_id (foreign key). We want to display a chart in our frontend that shows activity per day for all users.

The original SQL query only provides the total logins and time spent by each user over a specific date range, but we need more detailed information, including data broken down by each day.

The Original Query

The original query looks like this:

SELECT 
  user_id, 
  COUNT(user_id) AS total_logins,
  SUM(TIMEDIFF(completed_at, created_at)) AS total_time_spent 
FROM metrics_session 
WHERE created_at >= '2021-04-01' 
  AND created_at <= '2021-04-02'
  AND completed_at IS NOT NULL
GROUP BY user_id;

This query provides the total logins and time spent by each user, but it doesn’t break down the data by each day.

The Proposed Solution

To solve this problem, we need to modify the query to include date in our select statement and stop grouping by user_id to get a full picture without grouping by users. We’ll use the DATE_FORMAT function to format the dates as strings, which will allow us to group by each day.

Here’s an updated query:

SELECT 
  DATE_FORMAT(created_at, '%Y-%m-%d') AS day,
  COUNT(DISTINCT user_id) AS total_logins,
  SUM(TIMEDIFF(completed_at, created_at)) AS total_time_spent 
FROM metrics_session 
WHERE created_at >= '2021-04-01' 
  AND created_at <= '2021-04-02'
  AND completed_at IS NOT NULL
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d');

This query uses COUNT(DISTINCT user_id) to count the number of unique users per day, instead of grouping by user_id. This provides a more detailed view of the data.

Filtering Data for Single Users

If you want to display this chart only for a single user, you can add another filter to the query using the AND operator:

SELECT 
  DATE_FORMAT(created_at, '%Y-%m-%d') AS day,
  COUNT(DISTINCT user_id) AS total_logins,
  SUM(TIMEDIFF(completed_at, created_at)) AS total_time_spent 
FROM metrics_session 
WHERE created_at >= '2021-04-01' 
  AND created_at <= '2021-04-02'
  AND completed_at IS NOT NULL
  AND user_id = $your_user_id
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d');

Replace $your_user_id with the actual ID of the user you want to filter for.

Why This Query Works

This query works because it uses a combination of date formatting and grouping by each day. By using DATE_FORMAT, we can format the dates as strings, which allows us to group by each day without losing any data.

The COUNT(DISTINCT user_id) expression counts the number of unique users per day, instead of grouping by user_id. This provides a more detailed view of the data and allows us to break down the data by each day.

Conclusion

In this article, we explored how to get data within a specific date range, broken down by each day, using a single SQL query. We used the DATE_FORMAT function to format the dates as strings and grouped by each day using COUNT(DISTINCT user_id). We also discussed how to filter data for single users.

By following this approach, you can extract more detailed information from your database and create powerful visualizations that showcase your data in new ways.

Last modified on 2024-01-19