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

Прогноза на потрошувачка на артикли од магацинот според продажба во претходните недели, за подобра претпоставка за набавка на нови артикли

with WeeklySales as (
        select a.art_id,
                date_trunc('week', o.ord_date) as week_start,
                count(*) as units_sold
        from orders o
        join article_unit au on o.ord_id = au.ord_id 
        join unit_price up on au.unit_id = up.unit_id 
        join price p on up.price_id = p.price_id 
        join article a on p.art_id = a.art_id 
        where au.wh_id = 1
                and o.ord_date between current_date - interval '10 weeks' 
                        and current_date - interval '2 week'
        group by a.art_id, date_trunc('week',o.ord_date) 
),
AvgSales as (
        select art_id,
        avg(units_sold) as avg_units_sold
        from WeeklySales
        group by art_id
),
Inventory as (
select a.art_id, count(distinct au.unit_id) as currentStock
from article_unit au
         join warehouse w on au.wh_id = w.wh_id
         join unit_price up on au.unit_id = up.unit_id 
         join price p on up.price_id = p.price_id 
         join article a on p.art_id = a.art_id
where w.wh_id = 1
group by a.art_id
order by currentStock
)
select i.art_id,
        a.art_name,
        i.currentStock,
        avs.avg_units_sold,
        (i.currentStock - avs.avg_units_sold) as remaining_after_forecast
from Inventory i
join article a on i.art_id = a.art_id
join AvgSales avs on i.art_id = avs.art_id

Промена на број на продажби на секој артикл при промена на цена

with PriceChanges as (
        select
                p.art_id,
                p.price,
                p.price_eff_date,
                lag(p.price) over (partition by p.art_id order by p.price_eff_date) as prevPrice
        from price p
),
FilteredPrice as (
        select
                art_id,
                price,
                price_eff_date
        from PriceChanges
        where prevPrice is null or abs(price - prevPrice) >= 1
),
SalesByPrice as (
        select
                p.art_id,
                fp.price,
                fp.price_eff_date,
                count(au.unit_id) as units_sold,
                count(au.unit_id) * fp.price as revenue
        from article_unit au
        join orders o on au.ord_id = o.ord_id
        join unit_price up on up.unit_id = au.unit_id 
        join price p on up.price_id = p.price_id 
        join FilteredPrice fp on p.art_id = fp.art_id
                                                and p.price_eff_date = fp.price_eff_date
        where o.ord_date >= fp.price_eff_date
                and o.ord_date < coalesce(
                        (select min(fp2.price_eff_date)
                        from FilteredPrice fp2
                        where fp2.art_id = fp.art_id
                                and fp2.price_eff_date > fp.price_eff_date),
                        '9999-12-31'
                )
        group by p.art_id, fp.price, fp.price_eff_date
)
select
        a.art_name as articleName,
        sp.price as unitPrice,
        sp.price_eff_date as priceEffectiveDate,
        sp.units_sold,
        sp.revenue
from SalesByPrice sp
join article a on sp.art_id = a.art_id
order by a.art_name, sp.price_eff_date;

Споредба на потрошувачка по категорија по региони изразено во количина и приход

with ArticleSales as (
    select a.art_id,
           a.ctg_id,
           w.wh_id,
           c.city_id,
           r.region_id,
           p.price as unitPrice,
           count(au.unit_id) as unitsSold,
           count(au.unit_id)*p.price as income
    from article_unit au 
    join unit_price up on up.unit_id = au.unit_id 
    join price p on up.price_id = p.price_id
    join article a on p.art_id  = a.art_id 
    join warehouse w on au.wh_id = w.wh_id
    join city c on w.city_id = c.city_id
    join region r on c.region_id = r.region_id
    join orders o on au.ord_id = o.ord_id
    where o.ord_date>=p.price_eff_date
    group by a.art_id, a.ctg_id, w.wh_id, c.city_id, r.region_id, p.price, p.price
)
select r.region_name as region,
       cat.ctg_name as category,
       sum(asales.unitsSold) as totalUnitsSold,
       sum(asales.income) as totalIncome
from ArticleSales asales
join category cat on asales.ctg_id=cat.ctg_id
join region r on asales.region_id=r.region_id
group by r.region_name, cat.ctg_name
order by r.region_name, totalIncome desc;

Продажба на артикли во одреден магацин по квартали

select w.wh_id as warehouseId,
       w.wh_adr as warehouseAddress,
       c.city_name as warehouseCity,
       r.region_name as warehouseRegion,
       extract(year from o.ord_date) as "year",
       extract(quarter from o.ord_date) as "quarter",
       count(au.unit_id) as totalArticlesSold,
       sum(o.ord_sum) as totalSales
from warehouse w
join city c on w.city_id = c.city_id
join region r on c.region_id = r.region_id
join article_unit au on w.wh_id = au.wh_id
join orders o on au.ord_id = o.ord_id
group by w.wh_id, w.wh_adr, c.city_name, r.region_name, extract(year from o.ord_date), extract(quarter from o.ord_date)
order by w.wh_id,"year","quarter";

Годишен приход, профит и трошок по магацини

select w.wh_id as warehouseId,
       c.city_name as warehouseCity,
       r.region_name as warehouseRegion,
       extract(year from o.ord_date) as "year",
       sum(o.ord_sum) as totalIncome,
       sum(ord_sum)-sum(au.unit_cost_price) as total_profit
from warehouse w
         join city c on w.city_id = c.city_id
         join region r on c.region_id = r.region_id
         join article_unit au on w.wh_id = au.wh_id
         join orders o on au.ord_id = o.ord_id
group by w.wh_id, c.city_name, r.region_name, o.ord_date
order by w.wh_id,"year";

Аналитика на продукти по приход, потрошувачка, профит на определен период

with ProductSales as (select a.art_id,
                             a.art_name,
                             cat.ctg_name                           as category,
                             sum(p.price)                           as totalIncome,
                             sum(au.unit_cost_price)                as totalCost,
                             sum(p.price) - sum(au.unit_cost_price) as totalProfit,
                             count(au.unit_id)                      as totalUnitsSold
                      from article_unit au
                                join unit_price up on up.unit_id = au.unit_id 
                                join price p on up.price_id = p.price_id 
                                join article a on p.art_id = a.art_id
                            join category cat on a.ctg_id = cat.ctg_id
                            join orders o on au.ord_id = o.ord_id
                      where o.ord_date between '2024-01-01' and '2024-12-31'
                        and o.ord_date >= p.price_eff_date
                      group by a.art_id, a.art_name, cat.ctg_name)
select art_id,
       art_name,
       category,
       totalUnitsSold,
       totalIncome,
       totalCost,
       totalProfit
from ProductSales
order by totalProfit desc;