Walmart Inventory Status
Walmart is one of the world's largest retail chains with a variety of products ranging from electronics to groceries. Shoppers come to Walmart to buy their desired items, and the company tracks its sales meticulously to understand market trends and customer preferences.
You're given two tables, products and sales:
Write a SQL query that returns a table listing all products, and marks those products that haven't been sold yet.
The output should contain the following columns: product_id, product_name, sale_status in which sale_status can either be “Sold” or “Not Sold”.
This query will list all the products from the products table and join them with the sales table using a LEFT JOIN (to ensure even those products which haven't been sold are included). The resulting table will mark each product as 'Sold' or 'Not Sold' based on whether they appear in the sales table or not.
SELECT
p.product_id,
p.product_name,
CASE
WHEN s.product_id IS NULL THEN 'Not Sold'
ELSE 'Sold'
END AS sale_status
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name, s.product_id;
select DISTINCT p.product_id, p.product_name , CASE when sale_date is null then 'Not Sold' else 'Sold' END as sale_status from products p left join sales s on p.product_id= s.product_id