SALES +----------+----------+------------+------------+----------+---------+ | sale_id | store_id | product_id | sale_date | quantity | revenue | +----------+----------+------------+------------+----------+---------+ | 1 | 101 | 501 | 2024-06-01 | 3 | 75.00 | | 2 | 102 | 502 | 2024-06-02 | NULL | 40.00 | | 3 | 101 | 501 | 2024-06-03 | 2 | 50.00 | +----------+----------+------------+------------+----------+---------+ ​ ORDERS +----------+-------------+------------+--------------+ | order_id | customer_id | order_date | total_amount | +----------+-------------+------------+--------------+ | 1001 | 2001 | 2024-06-01 | 120.00 | | 1002 | 2002 | 2024-06-02 | 75.00 | | 1003 | 2001 | 2024-06-03 | 50.00 | +----------+-------------+------------+--------------+ ​ ORDER_ITEMS +----------+------------+----------+---------+ | order_id | product_id | quantity | revenue | +----------+------------+----------+---------+ | 1001 | 501 | 2 | 50.00 | | 1001 | 502 | 3 | 70.00 | | 1002 | 503 | 1 | 75.00 | +----------+------------+----------+---------+ ​ CUSTOMERS +-------------+--------+ | customer_id | region | +-------------+--------+ | 2001 | West | | 2002 | East | +-------------+--------+ ##### Scenario Retail analytics team needs ad-hoc reports on sales performance and customer behavior. ##### Question a) Given a single table SALES, write SQL to: • compute the average and standard deviation of revenue by product_id; • create a column that maps NULL quantity to 0 using CASE WHEN. b) Given three tables ORDERS, ORDER_ITEMS, CUSTOMERS, write SQL to: • join them to get total revenue per customer; • rank and dense-rank customers by total revenue within each region. ##### Hints Focus on GROUP BY, JOIN conditions, COALESCE/CASE, aggregate functions, and window functions (RANK/DENSE_RANK).