Напредни извештаи од базата (SQL и складирани процедури)

Неделен,Месечен,Годишен извештај

Weekly
Креираме прашалник каде велиме "за секој ден од неделата во последни 7 дена,
прикажи број нарачки и вкупен приход" :

SELECT TO_CHAR(order_date, 'Dy') AS label,
       COUNT(*) AS orders,
       SUM(total_price) AS revenue
FROM orders
WHERE order_date >= NOW() - INTERVAL '7 days'
GROUP BY label
ORDER BY MIN(order_date);

Monthly
Креираме прашалник каде велиме "за секој месец, преброј ги нарачките и пресметај вкупен приход" :

SELECT TO_CHAR(order_date, 'Mon') AS label,
       COUNT(*) AS orders,
       SUM(total_price) AS revenue
FROM orders
GROUP BY label
ORDER BY MIN(order_date);

Yearly
Креираме прашалник каде велиме "за секоја година, прикажи колку нарачки имало и колку приход е остварено" :

SELECT EXTRACT(YEAR FROM order_date)::INT AS label,
       COUNT(*) AS orders,
       SUM(total_price) AS revenue
FROM orders
GROUP BY label
ORDER BY label;

Најпродавани продукти,Топ клиенти,Нови регистрации по месец

Најпродавани продукти
Креираме прашалник каде велиме "за секој продукт, пресметај колку вкупно парчиња се продадени
(збир на quantity), сортирај ги и земи ги првите 5" :

SELECT 
  p.product_name AS label,
  SUM(c.quantity) AS total_sold
FROM contains c
JOIN stock s ON c.id_stock = s.id_stock
JOIN products p ON s.id_product = p.id_product
GROUP BY p.product_name
ORDER BY total_sold DESC
LIMIT 5;

Топ клиенти
Креираме прашалник каде велиме "да се пресмета колку трошел секој клиент вкупно,
и да се сортира според таа сума,на крај да ги врати ги првите 5" :

SELECT 
  c.first_name || ' ' || c.last_name AS label,
  SUM(o.total_price) AS total_spent
FROM orders o
JOIN customers c ON o.id_customer = c.id_customer
GROUP BY c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 5;

Нови регистрации по месец
Креираме прашалник каде велиме "за секој месец, прикажи колку нови корисници се регистрирале" :

SELECT 
  TO_CHAR(created_at, 'Mon YYYY') AS label,
  COUNT(*) AS new_users
FROM customers
GROUP BY label
ORDER BY MIN(created_at);

Предикција на производи кои наскоро ќе ги снема на залиха

Целта со оваа предикција е да направиме SQL query кое ќе ги излиста производите кои
веројатно ќе останат без залиха до крајот на неделата, врз основа на просечна неделна продажба.
Првин да потенцираме, ова стои само кога админот е најавен на страна Предикција, односно фајловите prediction.html и prediction.js.
Креираме повик до API за предикција на ризична залиха
кое е поставено на app.js како /admin/stock-alerts.

SELECT 
        p.id_product,
        p.product_name,
        sz.size_label AS size,
        COALESCE(SUM(s.quantity), 0) AS current_stock,
        ROUND(AVG(cq.weekly_sales), 2) AS avg_weekly_sales,
        CASE 
          WHEN COALESCE(SUM(s.quantity), 0) <= ROUND(AVG(cq.weekly_sales), 2) THEN 'ИТНО'
          ELSE 'Набљудувај'
        END AS status
      FROM products p
      JOIN stock s ON p.id_product = s.id_product
      JOIN sizes sz ON s.id_size = sz.id
      LEFT JOIN (
        SELECT 
          s.id_product,
          s.id_size,
          COUNT(*) AS weeks,
          SUM(c.quantity)::decimal / COUNT(DISTINCT DATE_TRUNC('week', o.order_date)) AS weekly_sales
        FROM contains c
        JOIN orders o ON c.id_order = o.id_order
        JOIN stock s ON c.id_stock = s.id_stock
        WHERE o.order_date >= NOW() - INTERVAL '4 weeks'
        GROUP BY s.id_product, s.id_size
      ) cq ON cq.id_product = s.id_product AND cq.id_size = s.id_size
      GROUP BY p.id_product, p.product_name, sz.size_label
      HAVING ROUND(AVG(cq.weekly_sales), 2) IS NOT NULL
      ORDER BY status DESC, avg_weekly_sales DESC;
    `);

Испраќаме авторизирано GET барање до backend за да добиеме податоци
за сите продукти и нивната состојба.
Ги добиваме сите продукти со следниве карактеристики:
product_name, size, current_stock, avg_weekly_sales, status.
Најпрво ќе бидат производите со најмногу продажба:

data.sort((a, b) => b.avg_weekly_sales - a.avg_weekly_sales);

Рачно правиме категоризација според редослед за јасен преглед:
Првите 3 → ИТНО
Следни 2 → Набљудувај
Останати → Стабилно