Купувач и Нарачки

Извештај за топ-купувачи

Идентификување на купувачите кои направиле најголем број нарачки или (вкупно) потрошиле најмногу.

SELECT 
    b.buyer_id, 
    b.company_name, 
    COUNT(o.order_id) AS total_orders, 
    COALESCE(SUM(i.total_amount), 0) AS total_spent
FROM 
    BUYER b
LEFT JOIN "ORDER" o ON b.buyer_id = o.buyer_id
LEFT JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY 
    b.buyer_id, b.company_name
ORDER BY 
    total_spent DESC, total_orders DESC;

Нарачки во очекување од одреден купувач

Наведени се сите неплатени нарачки групирани по купувачи, вклучувајќи ја и вкупната вредност.

SELECT 
    b.buyer_id, 
    b.company_name, 
    COUNT(o.order_id) AS pending_orders, 
    COALESCE(SUM(i.total_amount), 0) AS total_pending_value
FROM 
    BUYER b
LEFT JOIN "ORDER" o ON b.buyer_id = o.buyer_id
LEFT JOIN INVOICE i ON o.order_id = i.order_id
WHERE 
    o.status = 'Pending'
GROUP BY 
    b.buyer_id, b.company_name
ORDER BY 
    total_pending_value DESC;

Нарачки по земја

Собирање нарачки по земјата на купувач и прикажување на нивниот соодветен придонес на целокупниот приходот.

SELECT 
    b.country, 
    COUNT(o.order_id) AS total_orders, 
    COALESCE(SUM(i.total_amount), 0) AS total_revenue
FROM 
    BUYER b
LEFT JOIN "ORDER" o ON b.buyer_id = o.buyer_id
LEFT JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY 
    b.country
ORDER BY 
    total_revenue DESC, total_orders DESC;

Производ и Снабдувач

Најпродавани производи

Прикажување на најкупуваните производи - вкупен приход, продадена количина и поврзани производители.

• БЕЛЕШКА: Бидејќи нема колона за количина во ORDER_PRODUCT, се смета "times_ordered" како број на нарачки што го содржи тој производ.

SELECT
    p.product_id,
    p.name AS product_name,
    prod.company_name AS producer_name,
    COUNT(op.order_id) AS times_ordered,
    COALESCE(SUM(i.total_amount), 0) AS total_revenue
FROM PRODUCT p
JOIN PRODUCER prod ON p.producer_id = prod.producer_id
LEFT JOIN ORDER_PRODUCT op ON p.product_id = op.product_id
LEFT JOIN "ORDER" o ON op.order_id = o.order_id
LEFT JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY p.product_id, prod.company_name
ORDER BY total_revenue DESC, times_ordered DESC;

Приход по произведувач

Прикажување вкупнен приход генериран од секој производител врз основа на нарачките што ги вклучува неговите производи.

• БЕЛЕШКА: Се сумираат вкупните суми на уникатните фактура за да се избегне двојно броење на нарачката повеќекратно, ако има повеќе производи од ист производител.

SELECT
    pr.producer_id,
    pr.company_name,
    COALESCE(SUM(DISTINCT i.total_amount), 0) AS total_revenue
FROM PRODUCER pr
JOIN PRODUCT p ON pr.producer_id = p.producer_id
JOIN ORDER_PRODUCT op ON p.product_id = op.product_id
JOIN "ORDER" o ON op.order_id = o.order_id
JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY pr.producer_id, pr.company_name
ORDER BY total_revenue DESC;

Производи со слаби перформанси

Надевени се производите кои не се нарачани или имаат минимална продажба во последните 6 месеци. "Минимално" овде се дефинира како помалку од 5 нарачки во тој период. Овој временски период може да се смени.

SELECT 
    p.product_id,
    p.name AS product_name,
    pr.company_name AS producer_name,
    COUNT(o.order_id) AS order_count
FROM PRODUCT p
JOIN PRODUCER pr ON p.producer_id = pr.producer_id
LEFT JOIN ORDER_PRODUCT op ON p.product_id = op.product_id
LEFT JOIN "ORDER" o 
       ON op.order_id = o.order_id
      AND o.order_date >= CURRENT_DATE - INTERVAL '6 MONTH'
GROUP BY p.product_id, pr.company_name
HAVING COUNT(o.order_id) < 5
ORDER BY order_count ASC;

Детали за нарачка

Извештај за исполнување нарачка

Прикажување статус на испорака на секоја нарачка, вклучувајќи ги проценетите и датумите на испорака.

• БЕЛЕШКА: бидејќи нема колона actual_delivery_date, се споредуваат estimated_delivery_date (Табела ORDERS) и estimated_arrival (табела TRANSPORT). Полето status покажува дали е испорачано или не.

SELECT
    o.order_id,
    o.order_date,
    o.status AS order_status,
    o.estimated_delivery_date,
    t.estimated_departure,
    t.estimated_arrival,
    CASE
        WHEN o.status = 'Delivered' THEN 'Fulfilled'
        WHEN o.status IN ('Shipped', 'In Transit') THEN 'In Transit'
        ELSE 'Pending'
    END AS fulfillment_status
FROM "ORDER" o
LEFT JOIN TRANSPORT t ON o.transport_id = t.transport_id
ORDER BY o.order_date DESC;

Анализа на нарачки по временски период

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

• БЕЛЕШКА: се користи "estimated_delivery_date“ од ORDER. Може да се спореди и "transport.estimated_arrival".

SELECT
    o.order_id,
    o.order_date,
    o.estimated_delivery_date,
    (o.estimated_delivery_date - o.order_date) AS days_from_order_to_estimated_delivery,
    t.estimated_departure,
    t.estimated_arrival,
    (t.estimated_arrival - o.order_date) AS days_from_order_to_estimated_arrival
FROM "ORDER" o
LEFT JOIN TRANSPORT t ON o.transport_id = t.transport_id
ORDER BY o.order_id;

Детали за транспорт на нарачка

Извештај кој прикажува детали за транспортот (поаѓање, пристигнување и растојание) поврзани со секоја нарачка.

SELECT
    o.order_id,
    o.order_date,
    t.carrier_name,
    t.departure_point,
    t.arrival_point,
    t.estimated_departure,
    t.estimated_arrival,
    t.total_distance,
    t.special_requirements,
    t.insurance_conditions,
    t.incoterm
FROM "ORDER" o
JOIN TRANSPORT t ON o.transport_id = t.transport_id
ORDER BY o.order_date DESC;

Фактура и Наплата

Извештај за неплатени фактури

Наведени се сите неплатени фактури со нивната соодветна нарачка и детали за купувачот. Претпоставува статус "Неплатен" или "Во тек" во табелата INVOICE или статус на плаќање различен од "payed" во табелата PAYMENT. Ако информациите строго зависат од invoice.status, филтрирањето е "статус <---> платено".

SELECT
    i.invoice_key,
    i.invoice_date,
    i.status AS invoice_status,
    p.due_date AS payment_due_date,
    p.payment_status,
    b.buyer_id,
    b.company_name AS buyer_name,
    b.billing_address
FROM INVOICE i
JOIN "ORDER" o ON i.order_id = o.order_id
JOIN BUYER b ON o.buyer_id = b.buyer_id
JOIN PAYMENT p ON o.order_id = p.order_id
WHERE p.payment_status NOT IN ('Paid')
ORDER BY p.due_date DESC;

Анализа на задоцнети плаќања

Прикажани се сите нарачки чиј плаќања се извршени по крајниот датумот, вклучително и примерок од пресметката на задоцнетите трошоци. Може да се промени дневната стапка.

SELECT
    p.payment_id,
    p.order_id,
    p.amount,
    p.due_date,
    p.payment_date,
    CASE 
        WHEN p.payment_date > p.due_date THEN (p.payment_date - p.due_date)
        ELSE 0
    END AS days_late,
    CASE 
        WHEN p.payment_date > p.due_date THEN (p.amount * 0.01 * (p.payment_date - p.due_date))
        ELSE 0
    END AS late_fee
FROM PAYMENT p
WHERE p.payment_date > p.due_date;

Преглед на статусот на плаќање

Агрегација и прикажување процент на плаќања кои се во различни статуси.

SELECT
    payment_status,
    COUNT(*) AS count_payments,
    SUM(amount) AS total_amount,
    ROUND(
        (COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ()), 2
    ) AS percentage_of_payments
FROM PAYMENT
GROUP BY payment_status
ORDER BY count_payments DESC;

Финансиска Анализа

Трендови на приходите

Месечни или квартални движења на приходите генерирани од нарачки. Ова се заснова на табелата INVOICE (invoice_date, total_amount). За месечни трендови, користиме DATE_TRUNC (month, i.invoice_date)

• БЕЛЕШКА: За квартални трендови, се заменува month со quarter.

SELECT
    DATE_TRUNC('month', i.invoice_date) AS month,
    SUM(i.total_amount) AS monthly_revenue
FROM INVOICE i
GROUP BY DATE_TRUNC('month', i.invoice_date)
ORDER BY month;

Анализа на валути

Распределба на плаќањата примени во различни валути, претворајќи ги во единствена референтна валута (на пр. USD) со користење на девизен курс.

SELECT
    p.currency,
    COUNT(*) AS total_payments,
    SUM(p.amount) AS total_amount_in_currency,
    SUM(p.amount * p.exchange_rate) AS total_amount_converted -- i.e. to USD
FROM PAYMENT p
GROUP BY p.currency;

Даночни придонеси

Апроксимација на даночните придонеси по купувач или производител. Бидејќи нема експлицитна даночна стапка во базата на податоци, претпоставена е хипотетичка стапка (на пр., 20%). Овој пример ја пресметува вкупната вредност на фактурата по купувач и се множи со 20%.

SELECT
    b.buyer_id,
    b.company_name,
    b.country,
    COALESCE(SUM(i.total_amount), 0) AS total_sales,
    COALESCE(SUM(i.total_amount) * 0.20, 0) AS estimated_tax_contribution
FROM BUYER b
JOIN "ORDER" o ON b.buyer_id = o.buyer_id
JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY b.buyer_id, b.company_name, b.country
ORDER BY total_sales DESC;

Транспорт и логистика

Перформанси на превозникот

Анализа на перформансите на секој превозник врз основа на бројот на пратки, просечното растојание и просечното време на транзит.

SELECT
    t.carrier_name,
    COUNT(o.order_id) AS total_shipments,
    COALESCE(AVG(t.total_distance), 0) AS avg_distance,
    COALESCE(AVG(t.estimated_arrival - t.estimated_departure), 0) AS avg_transit_days
FROM TRANSPORT t
LEFT JOIN "ORDER" o ON t.transport_id = o.transport_id
GROUP BY t.carrier_name
ORDER BY total_shipments DESC;

Анализа на incoterm

Се прикажива распределбата на нарачките по incoterms, заедно со вкупните износи на фактурата.

SELECT
    t.incoterm,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(i.total_amount), 0) AS total_invoice_amount
FROM TRANSPORT t
LEFT JOIN "ORDER" o ON t.transport_id = o.transport_id
LEFT JOIN INVOICE i ON o.order_id = i.order_id
GROUP BY t.incoterm
ORDER BY total_orders DESC;

Ефикасност на рутата

Оценување на просечното растојание и време на транзит по рута (departure_point, arrival_point).

SELECT
    t.departure_point,
    t.arrival_point,
    COUNT(o.order_id) AS total_orders,
    COALESCE(AVG(t.total_distance), 0) AS avg_distance,
    COALESCE(AVG(t.estimated_arrival - t.estimated_departure), 0) AS avg_transit_time
FROM TRANSPORT t
LEFT JOIN "ORDER" o ON t.transport_id = o.transport_id
GROUP BY t.departure_point, t.arrival_point
ORDER BY total_orders DESC;

Усогласенот и ревизија

Усогласеност со извозот на производителот

Идентификација на производителите со нецелосни или невалидни детали за лиценцата за извоз. За демонстрација, наведени се сите производители кои имаат NULL или празен export_license_number.

SELECT
    producer_id,
    company_name,
    export_license_number
FROM PRODUCER
WHERE export_license_number IS NULL
   OR export_license_number = '';

Детален преглед на фактури

Целосен преглед на фактури, вклучувајќи ги поврзаните нарачки, плаќања и тековниот статус.

SELECT
    i.invoice_key,
    i.invoice_date,
    i.status AS invoice_status,
    i.total_amount,
    o.order_id,
    o.status AS order_status,
    p.payment_id,
    p.payment_status,
    p.payment_method
FROM INVOICE i
JOIN "ORDER" o ON i.order_id = o.order_id
LEFT JOIN PAYMENT p ON o.order_id = p.order_id
ORDER BY i.invoice_date DESC;

Несовпаѓање на плаќањa

Идентификација на нарачки каде збирот на плаќања не се совпаѓа со вкупниот износ на фактурата.

SELECT
    i.invoice_key,
    i.order_id,
    i.total_amount AS invoice_total,
    COALESCE(SUM(p.amount), 0) AS total_payment,
    (i.total_amount - COALESCE(SUM(p.amount), 0)) AS difference
FROM INVOICE i
LEFT JOIN PAYMENT p ON i.order_id = p.order_id
GROUP BY i.invoice_key, i.order_id, i.total_amount
HAVING i.total_amount <> COALESCE(SUM(p.amount), 0)
ORDER BY difference DESC;