I have a SQL puzzle:
How can we get a list of customers who have purchased 3 or fewer socks (including those who haven't purchased any socks) as well how many socks those customers have purchased?
I am running into a problem where I ended up with NULL values that need to be displayed as zeroes. However, when I use CASE statement to select NULLs as zeroes and then use WHEN statement to filter below certain threshold zeroes do not show up for some reason.
Here is my schema and some dummy values:
create table customers ( cust_id TEXT, name TEXT ); create table orders ( order_id TEXT, cust_id TEXT, order_date DATE, num_socks INT ); insert into customers values ('1001', 'Rudy'); insert into customers values ('1002', 'Jonny'); insert into customers values ('1003', 'Romeo'); insert into orders values ('1', '1001', '2015-05-01', 4); insert into orders values ('2', '1001', '2015-04-03', 2); insert into orders values ('3', '1003', '2015-04-10', 8); insert into orders values ('4', '1003', '2016-05-10', 3);
Here is my query:
SELECT name, CASE WHEN socks_bought IS NULL THEN 0 ELSE socks_bought END AS socks_bought FROM customers LEFT JOIN (SELECT cust_id, sum(num_socks) AS socks_bought FROM orders GROUP BY cust_id) AS t1 ON t1.cust_id = customers.cust_id where socks_bought < 3;
However, it does not produce the desired output:
name | socks_bought ------------------- Jonny | 0
What am I doing wrong? What is the query to produce the intended results? Thanks.