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

Потрошувачка по коминтент за даден период по производ, по количина и пари

with latestPrice as (
    select p.art_id,p.price as latest_price
    from price p
    where p.price_eff_date = (
        select max(price_eff_date)
        from price
        where p.art_id=p.art_id
        )
)
select c.cust_company_name as customerName,
       c.user_id as customerId,
       a.art_name as articleName,
       count(au.unit_id) as quantity,
       lp.latest_price as unitPrice,
       count(au.unit_id)*lp.latest_price as total_spent,
       sum(o.ord_sum) as orderTotal
from orders o
join article_unit au on o.ord_id = au.ord_id
join article a on au.art_id = a.art_id
join customer c on o.cust_id = c.user_id
join latestPrice lp on a.art_id=lp.art_id
where o.ord_date between '2024-01-01' and '2024-12-31'
group by c.cust_company_name,c.user_id,a.art_name,lp.latest_price
order by c.cust_company_name,a.art_name;

Артикли кои се при крај на залиха во магацинот и треба да се набават дополнителни количини

select a.art_name as articleName, w.wh_id as warehouseId, count(distinct au.unit_id) as unitNumber
from article_unit au
         join warehouse w on au.wh_id = w.wh_id
         join article a on au.art_id = a.art_id
where w.wh_id = 1
group by a.art_name, w.wh_id
order by unitNumber;

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

with salesByPrice as (select au.art_id,
                             p.price,
                             p.price_eff_date,
                             count(au.unit_id)           as unitsSold,
                             count(au.unit_id) * p.price as revenue
                      from article_unit au
                               join orders o on au.ord_id = o.ord_id
                               join price p on au.art_id = p.art_id
                      where o.ord_date >= p.price_eff_date
                        and o.ord_date < coalesce(
                              (select min(price_eff_date)
                               from price p2
                               where p2.art_id = p.art_id
                                 and p2.price_eff_date > p.price_eff_date),
                              '9999-12-31'
                                         )
                      group by au.art_id, p.price, p.price_eff_date)
select a.art_name        as articleName,
       sp.price          as unitPrice,
       sp.price_eff_date as priceEffectiveDate,
       sp.unitsSold,
       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 au.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 article a on au.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 price p on a.art_id = p.art_id
    join orders o on au.ord_id = o.ord_id
    where o.ord_date>=p.price_eff_date
    group by au.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 au.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 article a on au.art_id = a.art_id
                               join category cat on a.ctg_id = cat.ctg_id
                               join price p on a.art_id = p.art_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 au.art_id, a.art_name, cat.ctg_name)
select art_id,
       art_name,
       category,
       totalUnitsSold,
       totalIncome,
       totalCost,
       totalProfit
from ProductSales
order by totalProfit desc;