Intern

SQL tasks 1) Display the number of orders with status "processing" for each executor in March 2024, ONLY for high-priority orders (priority = 'high'). Sort by descending order count. Exclude executors with 0 orders. 2) Show the number of successfully completed orders by categories, where the average revenue is > 70000, considering only customer orders with ID >= 1002. Exclude categories with unknown average revenue. 3) Add a column 'rank' to the orders_log table, ranking records for each executor by timestamp in ascending order, but only for orders with status "pending" or "processing". Example result (executor=1):

sobes.tech AI

Answer from AI

Number of orders with status "processing" for each executor in March 2024 for priority orders:

SELECT executor, COUNT(*) AS orders_count
FROM orders
WHERE status = 'processing'
  AND priority = 'high'
  AND order_date >= '2024-03-01' AND order_date < '2024-04-01'
GROUP BY executor
HAVING COUNT(*) > 0
ORDER BY orders_count DESC;

Number of successfully completed orders by categories where average revenue > 70000, considering only clients with ID >= 1002:

SELECT category, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
  AND client_id >= 1002
GROUP BY category
HAVING AVG(revenue) > 70000;

Adding a rank column to the orders_log table for ranking records by timestamp for each executor, but only for orders with status "pending" or "processing":

ALTER TABLE orders_log ADD COLUMN rank INT;

WITH ranked AS (
  SELECT id, 
         ROW_NUMBER() OVER (PARTITION BY executor ORDER BY timestamp ASC) AS rn
  FROM orders_log
  WHERE status IN ('pending', 'processing')
)
UPDATE orders_log ol
SET rank = r.rn
FROM ranked r
WHERE ol.id = r.id;

If dynamic ranking output without changing structure is needed, a window function can be used in SELECT.

Previous Next