Postgresql - COUNT with WHERE condition doesn't return all rows

lana222
October 22, 2024
186 views
0 votes
2 Answers

I’m writing a solution for a DataLemur SQL question and I almost had it right but there is one row missing.

I have written this query myself:

WITH CTE AS
(
SELECT 
  user_id,
  transaction_date,
  DENSE_RANK() OVER (PARTITION BY user_id order by transaction_date) as ranked_transact
FROM user_transactions
)
SELECT 
  transaction_date,
  user_id,
  COUNT(user_id) as purchase_count
FROM CTE
WHERE ranked_transact IN (SELECT MAX(ranked_transact) FROM CTE)
GROUP BY user_id, transaction_date

Then, when I submitted I have seen that I’m missing a row: for some reason, the WHERE clause condition didn’t work for user_id = 115.

The desired results were supposed to be:

transaction_date	user_id	purchase_count
07/11/2022 10:00:00	123	1
07/12/2022 10:00:00	115	1
07/12/2022 10:00:00	159	2

But my query returned:

transaction_date	user_id	purchase_count
07/11/2022 10:00:00	123	1
07/12/2022 10:00:00	159	2

I checked the solution and saw that they ordered the ranking by descending order of transaction_date and then they used WHERE transaction_date=1 . In my query, I ordered the ranking by ascending order and asked the select statement to return the max ranking value which is supposed to give me the most recent transaction row, no?

I’m probably missing something silly but can someone explain to me where lies the problem and why is user_id 115 not included when I use the count function?

Thank you!

Answers

The MAX(ranked_transact) depends on the user_id. In your code you only select the records where the ranked_transact is equal to the MAX()`, which is only true for the 2 records shown.

CREATE TABLE user_transactions ( 
   product_id INTEGER ,
   user_id INTEGER ,
   spend DECIMAL(8,2) ,
   transaction_date TIMESTAMP );

INSERT INTO  user_transactions  VALUES
('3673','123','68.90','07/08/2022 10:00:00'),
('9623','123','274.10','07/08/2022 10:00:00'),
('1467','115','19.90','07/08/2022 10:00:00'),
('2513','159','25.00','07/08/2022 10:00:00'),
('1452','159','74.50','07/10/2022 10:00:00'),
('1452','123','74.50','07/10/2022 10:00:00'),
('9765','123','100.15','07/11/2022 10:00:00'),
('6536','115','57.00','07/12/2022 10:00:00'),
('7384','159','15.50','07/12/2022 10:00:00'),
('1247','159','23.40','07/12/2022 10:00:00');


WITH CTE AS
(
SELECT 
  user_id,
  transaction_date,
  DENSE_RANK() OVER (PARTITION BY user_id order by transaction_date) as ranked_transact
FROM user_transactions
)
-- select * from CTE;
SELECT 
  transaction_date,
  user_id,
  COUNT(user_id) as purchase_count
FROM CTE
WHERE (user_id,ranked_transact) IN (SELECT user_id,MAX(ranked_transact) FROM CTE group by user_id)
GROUP BY user_id, transaction_date
;

Based on their most recent transaction date, write a query that
retrieve the users along with the number of products they bought.

Output the user’s most recent transaction date, user ID, and the
number of products, sorted in chronological order by the transaction
date.

DENSE_RANK would produce 1 for every recent transaction date, later used on the WHERE condition

Query,

WITH latest_purchase AS (
      SELECT user_id,
             transaction_date,
             DENSE_RANK() OVER(PARTITION BY user_id ORDER BY transaction_date DESC) rn
      FROM user_transactions
) SELECT transaction_date,
         user_id,
         COUNT(user_id) as purchase_count
  FROM latest_purchase
  WHERE rn = 1
  GROUP BY transaction_date,user_id
  ORDER BY user_id ASC;

Data Example

CREATE TABLE user_transactions ( 
   product_id INTEGER ,
   user_id INTEGER ,
   spend DECIMAL(8,2) ,
   transaction_date TIMESTAMP );

INSERT INTO  user_transactions  VALUES
('3673','123','68.90','07/08/2022 10:00:00'),
('9623','123','274.10','07/08/2022 10:00:00'),
('1467','115','19.90','07/08/2022 10:00:00'),
('2513','159','25.00','07/08/2022 10:00:00'),
('1452','159','74.50','07/10/2022 10:00:00'),
('1452','123','74.50','07/10/2022 10:00:00'),
('9765','123','100.15','07/11/2022 10:00:00'),
('6536','115','57.00','07/12/2022 10:00:00'),
('7384','159','15.50','07/12/2022 10:00:00'),
('1247','159','23.40','07/12/2022 10:00:00');

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – COUNT with WHERE condition doesn't return all rows

Answers