Update based on subquery aggregate
SQL
Hard
2 views
Problem Description
Give a 5% discount on Products that have not sold in the last 180 days. Use Orders + OrderItems to find what sold recently.
Output Format
DML statement(s)
Sample Test Case
Input:
Products, Orders, OrderItems
Constraints
Assume PostgreSQL-style interval
Official Solution
UPDATE Products SET price = price * 0.95 WHERE product_id NOT IN (SELECT DISTINCT oi.product_id FROM OrderItems oi JOIN Orders o ON o.order_id = oi.order_id WHERE o.order_date >= (CURRENT_DATE - INTERVAL '180 days'));
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!