Why is my SQL query returning blank rows?-CodePudding

I am trying to work on the following problem: "Write a query to identify customers who place more than three transactions each in both 2019 and 2020."

I have the following tables transaction table: ID, user_id, created_at, "Date" product_id, quantity

users table: id, name

I created two separate CTE's, one for 2019 and one for 2020 then I try to use a WHERE IN clause to see which names are in 2019 and 2020 and my results come back blank. The answer shows there should be names coming back in my code output.

WITH CTE_2019 AS (
    SELECT
        name,
        created_at
    FROM (
        SELECT
            t.user_id,
            u.name,
            t.created_at
        FROM users u
        JOIN transactions t
        ON u.id = t.user_id
        GROUP BY t.user_id
        HAVING COUNT(DISTINCT t.id) > 3
        ) AS subquery_1
    WHERE YEAR(created_at) = 2019),

CTE_2020 AS (
    SELECT
        name,
        created_at
    FROM (
        SELECT
            t.user_id,
            u.name,
            t.created_at
        FROM users u
        JOIN transactions t
        ON u.id = t.user_id
        GROUP BY t.user_id
        HAVING COUNT(DISTINCT t.id) > 3
        ) AS subquery_2
    WHERE YEAR(created_at) = 2020)

SELECT
    DISTINCT name
FROM CTE_2019
WHERE name IN (
    SELECT
        DISTINCT name
    FROM CTE_2020
)

CodePudding user response：

I figured out the answer. I needed to move my WHERE clause FROM outside the subquery to inside.

WITH CTE_2019 AS (
    SELECT
        name,
        created_at
    FROM (
        SELECT
            t.user_id,
            u.name,
            t.created_at
        FROM users u
        JOIN transactions t
        ON u.id = t.user_id
        WHERE YEAR(t.created_at) = 2019
        GROUP BY t.user_id
        HAVING COUNT(DISTINCT t.id) > 3
        ) AS subquery_1),

CTE_2020 AS (
    SELECT
        name,
        created_at
    FROM (
        SELECT
            t.user_id,
            u.name,
            t.created_at
        FROM users u
        JOIN transactions t
        ON u.id = t.user_id
        WHERE YEAR(t.created_at) = 2020
        GROUP BY t.user_id
        HAVING COUNT(DISTINCT t.id) > 3
        ) AS subquery_2)

SELECT
    name AS customer_name
FROM users
WHERE name IN (
    SELECT
        DISTINCT name
    FROM CTE_2019
    UNION ALL
    SELECT
        DISTINCT name
    FROM CTE_2020
)

CodePudding user response：

This will give the desired results in a much simplified way...

SELECT u.name AS customer_name
  FROM users u
  JOIN transactions t
    ON u.id = t.user_id
 GROUP BY u.name
HAVING COUNT(CASE WHEN YEAR(t.created_at) = 2020 THEN 1 END) > 3
   AND COUNT(CASE WHEN YEAR(t.created_at) = 2019 THEN 1 END) > 3

Super Simple Example:

Schema (MySQL v8.0)

CREATE TABLE transactions
    (`id` int, `user_id` int, `created_at` int)
;
    
INSERT INTO transactions
    (`id`, `user_id`, `created_at`)
VALUES
    (0, 1, 2019),
    (1, 1, 2019),
    (2, 1, 2019),
    (3, 1, 2020),
    (4, 1, 2020),
    (5, 1, 2020),
    (6, 2, 2019),
    (7, 3, 2020),
    (8, 3, 2020),
    (9, 3, 2020),
    (10, 4, 2020)
;


CREATE TABLE users
    (`id` int, `name` varchar(5))
;
    
INSERT INTO users
    (`id`, `name`)
VALUES
    (1, 'bob'),
    (2, 'joe'),
    (3, 'sally'),
    (4, 'harry')
;

Query #1

SELECT u.name AS customer_name
  FROM users u
  JOIN transactions t
    ON u.id = t.user_id
 GROUP BY u.name
HAVING COUNT(CASE WHEN t.created_at = 2020 THEN 1 END) > 2
   AND COUNT(CASE WHEN t.created_at = 2019 THEN 1 END) > 2;

customer_name
bob

View on DB Fiddle