Counting Observations Week by Week as They Get Updated with SQL

Counting Observations Week by Week as They Get Updated

Introduction

In this blog post, we will explore a problem that involves counting observations week by week as they get updated. We will use SQL to solve this problem and provide an example solution.

Problem Statement

The problem statement is as follows:

“I built a table frame to count observations week by week and track the change that occur.”

A sample table structure is provided, which includes two tables: MMWR and MMWR_CATEGORY. The MMWR table contains information about each observation, including the week number and end date. The MMWR_CATEGORY table contains a list of categories for products.

The task is to write a SQL query that counts the observations week by week as they get updated.

Solution

To solve this problem, we can use a combination of SQL techniques, including joins, subqueries, and grouping. Here’s an example solution:

Step 1: Create Cross Joined Table

We start by creating a cross joined table between MMWR and MMWR_CATEGORY. This will allow us to combine the data from both tables.

WITH grid AS (
    SELECT m.*, c.* 
    FROM MMWR m 
    CROSS JOIN MMWR_CATEGORY c
    ORDER BY m.MMWR_WEEK, c.PROD
)

Step 2: Prepare Data for Future Join

Next, we prepare the data from tbl (the temporary table containing the observations) for a future join with our cross joined table. We do this by creating another CTE called prod_use.

 prod_use AS (
    SELECT PATIENT_UUID "PATIENT_ID", PR_AGE_1 "AGE", PR_1 "PROD", PR_DT_1 "DATE_OF_USE" 
    FROM tbl 
    UNION ALL 
    SELECT PATIENT_UUID "PATIENT_ID", PR_AGE_2 "AGE", PR_2 "PROD", PR_DT_2 "DATE_OF_USE" 
    FROM tbl 
    UNION ALL 
    SELECT PATIENT_UUID "PATIENT_ID", PR_AGE_3 "AGE", PR_3 "PROD", PR_DT_3 "DATE_OF_USE" 
    FROM tbl
 )

Step 3: Join Cross Joined Table with Data

Now we join the cross joined table with our prepared data.

SELECT g.*,
CASE WHEN u.PROD Is Not Null Then 1 ELSE 0 END "USE_OF_PROD"
FROM grid g
LEFT JOIN 
prod_use u ON(u.PROD = g.PROD AND u.AGE = g.AGE AND u.DATE_OF_USE Between g.END_DATE - INTERVAL '6' DAY And g.END_DATE)
ORDER BY 
g.MMWR_WEEK, g.PROD, g.AGE

Step 4: Result

The result of the above query is a table that counts the observations week by week as they get updated.

Conclusion

In this blog post, we explored a problem involving counting observations week by week as they get updated. We used SQL to solve this problem and provided an example solution.

By using a combination of SQL techniques, including joins, subqueries, and grouping, we were able to count the observations week by week as they get updated.

Additional Notes

Always make sure that your data columns have appropriate types and values. There are some differences in your question regarding this - like ‘Product 1’ and ‘Product1’ - they are not the same. Be especially carefull with the columns of type DATE too.
You should be careful when using subqueries, especially with joins, since they can lead to performance issues if not used correctly.
Always use meaningful table names and column names in your SQL queries.

Full Code

WITH 
    grid AS 
        (
            SELECT m.*, c.* 
            FROM MMWR m 
            CROSS JOIN MMWR_CATEGORY c
            ORDER BY m.MMWR_WEEK, c.PROD
        ),
    
    prod_use AS (
        SELECT PATIENT_UUID "PATIENT_ID", PR_AGE_1 "AGE", PR_1 "PROD", PR_DT_1 "DATE_OF_USE" 
        FROM tbl 
        UNION ALL 
        SELECT PATIENT_UUID "PATIENT_ID", PR_AGE_2 "AGE", PR_2 "PROD", PR_DT_2 "DATE_OF_USE" 
        FROM tbl 
        UNION ALL 
        SELECT PATIENT_UUID "PATIENT_ID", PR_AGE_3 "AGE", PR_3 "PROD", PR_DT_3 "DATE_OF_USE" 
        FROM tbl
    )

SELECT
    g.*,
    CASE WHEN u.PROD Is Not Null Then 1 ELSE 0 END "USE_OF_PROD"
FROM
    grid g
LEFT JOIN 
    prod_use u ON(u.PROD = g.PROD AND u.AGE = g.AGE AND u.DATE_OF_USE Between g.END_DATE - INTERVAL '6' DAY And g.END_DATE)
ORDER BY
    g.MMWR_WEEK, g.PROD, g.AGE

Last modified on 2023-08-30