SQL Solution test

1. SELECT p.vend_id, v.vend_name
FROM Products p 
LEFT JOIN Vendors v ON p.vend_id = v.vend_id
LEFT JOIN OrderItems oi ON p.prod_id = oi.prod_id
LEFT JOIN Orders o ON oi.order_num = o.order_num
WHERE YEAR(o.order_date) = «2021» AND p.prod_desc NOT LIKE %зонт%
AND p.prod_id IS NULL;

2. SELECT 
  YEAR(o.order_date) AS year, 
  CASE
    WHEN v.vend_country = 'USA' THEN 'Американские поставщики'
    ELSE 'Неамериканские поставщики'
  END AS vendor_type,
  AVG(oi.item_price * oi.quantity) AS avg
FROM OrderItems oi
JOIN Orders o ON oi.order_num = o.order_num
JOIN Products p ON oi.prod_id  = p.prod_id
JOIN Vendors v ON p.vend_id  = v.vend_id
GROUP BY year, v.vend_country;

3.(a)

with prep as 
(SELECT
  CONCAT(FLOOR(oi.total_cost / 5000) * 5000, ' - ', FLOOR(oi.total_cost / 5000 + 1) * 5000 - 1) AS cost_range,
  COUNT(*) AS order_count
FROM (
  SELECT oi.order_num, SUM(oi.quantity * oi.item_price) AS total_cost
  FROM orderItems oi
  GROUP BY oi.order_num
) oi
GROUP BY oi.total_cost
ORDER BY FLOOR(oi.total_cost / 5000))

SELECT
  cost_range,
  sum(order_count) as total_order_count
from prep
group by cost_range

**(b вариант generates_series в postgersql)**

WITH order_summary AS (
  SELECT oi.order_num, SUM(oi.quantity * oi.item_price) AS total_cost
  FROM orderItems oi
  GROUP BY oi.order_num
)
SELECT
  CONCAT(FLOOR(ranges.range / 5000) * 5000, 
    ' - ', 
    FLOOR(ranges.range / 5000 + 1) * 5000 - 1) AS cost_range,
  COUNT(os.total_cost) AS order_count
FROM (
  SELECT generate_series(0, cast(CEIL(MAX(total_cost) / 5000) * 5000 - 1 as int), 5000) AS range
  from order_summary os
) ranges LEFT JOIN order_summary os on os.total_cost >= ranges.range AND os.total_cost < ranges.range + 5000
GROUP BY ranges.range
ORDER BY 1;