project_p2

-- 1
SELECT product_name,
       availability
FROM product_shop.product
WHERE availability < 3
ORDER BY availability;

--2
SELECT AVG(sum) --*, DATE_TRUNC('month', CAST(payment_date AS timestamp))----
FROM product_shop.user_order
--WHERE DATE_TRUNC('month', CAST(payment_date AS timestamp)) IN ('2022-06-01','2022-07-01','2022-08-01')
WHERE CAST(order_date AS timestamp) BETWEEN '2022-06-01' AND '2022-08-31';

--3
SELECT p.product_name,
       p.product_description,
       p.price,
       p.availability --SUM(p.price * p.availability)--
FROM product_shop.product p
LEFT OUTER JOIN product_shop.product_order po ON p.product_id = po.product_id
WHERE order_id IS NULL;

--4
SELECT p.product_name top_names,
       po.product_id,
       count(po.order_id) order_cnt
FROM product_shop.product_order po
LEFT OUTER JOIN product_shop.product p ON p.product_id = po.product_id
GROUP BY 1,
         2
ORDER BY count(po.order_id) DESC
LIMIT 10;

--5
SELECT surname,
       name,
       middle_name,
       COUNT(order_id) order_cnt
FROM product_shop.user_order_detail
GROUP BY 1,
         2,
         3
HAVING COUNT(order_id) > 1
ORDER BY order_cnt DESC;


--6
SELECT c.category_name,
       c.category_description,
       count(o.order_id) order_cnt
FROM product_shop.user_order o
LEFT OUTER JOIN product_shop.product_order po ON o.order_id = po.order_id
LEFT OUTER JOIN product_shop.product p ON po.product_id = p.product_id
LEFT OUTER JOIN product_shop.category c ON c.category_id = p.category_id
WHERE DATE_TRUNC('month', CAST(o.order_date AS timestamp)) IN ('2022-06-01',
                                                               '2022-07-01',
                                                               '2022-08-01')
GROUP BY 1,
         2
ORDER BY order_cnt DESC
LIMIT 1;

--6 v.2

SELECT c.category_name,
       c.category_description,
       count(o.order_id) order_cnt
FROM product_shop.user_order o, product_shop.product_order po, product_shop.product p, product_shop.category c
WHERE o.order_id = po.order_id
  AND po.product_id = p.product_id
  AND c.category_id = p.category_id
  AND DATE_TRUNC('month', CAST(o.order_date AS timestamp)) IN ('2022-06-01',
                                                               '2022-07-01',
                                                               '2022-08-01')
GROUP BY 1,
         2
ORDER BY order_cnt DESC
LIMIT 1;