Advanced SQL Queries and Reports for Invoices

1. Годишна споредба на приходи

WITH revenue_by_year AS (
  SELECT 
    EXTRACT(YEAR FROM issue_date) AS year,
    SUM(total_amount) AS total_revenue
  FROM 
    Invoice
  GROUP BY 
    EXTRACT(YEAR FROM issue_date)
)
SELECT 
  current.year AS current_year,
  current.total_revenue AS current_revenue,
  previous.total_revenue AS previous_revenue,
  ROUND(
    (current.total_revenue - previous.total_revenue) * 100.0 / NULLIF(previous.total_revenue, 0),
    2
  ) AS growth_percentage
FROM 
  revenue_by_year current
LEFT JOIN 
  revenue_by_year previous ON current.year = previous.year + 1
ORDER BY current_year DESC
LIMIT 1;

2. Финансиски преглед на агенцијата (тековна година)

SELECT 
  COUNT(DISTINCT c.client_id) AS total_clients,
  COUNT(i.invoice_id) AS total_invoices,
  SUM(i.total_amount) AS total_revenue,
  SUM(i.invoice_taxes) AS total_tax_collected,
  ROUND(AVG(i.total_amount), 2) AS avg_invoice_value
FROM 
  Invoice i
JOIN Client c ON i.client_id = c.client_id
WHERE 
  EXTRACT(YEAR FROM i.issue_date) = EXTRACT(YEAR FROM CURRENT_DATE);

3. Најуспешна компанија според просечна вредност на фактури

SELECT 
  c.company_id,
  c.company_name,
  ROUND(AVG(i.total_amount), 2) AS avg_invoice_amount,
  COUNT(i.invoice_id) AS invoice_count
FROM 
  Invoice i
JOIN Company c ON i.company_id = c.company_id
GROUP BY 
  c.company_id, c.company_name
ORDER BY 
  avg_invoice_amount DESC
LIMIT 5;

4. Годишен Извештај за Приходи по Месеци (за секоја компанија)

SELECT 
  c.company_id,
  c.company_name,
  TO_CHAR(i.issue_date, 'YYYY-MM') AS month,
  COUNT(i.invoice_id) AS num_invoices,
  SUM(i.total_amount) AS monthly_revenue
FROM 
  Invoice i
JOIN Company c ON c.company_id = i.company_id
GROUP BY 
  c.company_id, c.company_name, TO_CHAR(i.issue_date, 'YYYY-MM')
ORDER BY 
  c.company_name, month;

5. Извештај за Клиентски Перформанси – Побарани vs Добиени Средства

SELECT 
  cl.client_id,
  cl.client_name,
  COUNT(i.invoice_id) AS total_invoices,
  SUM(i.total_amount) AS total_requested,
  SUM(CASE WHEN i.status = 'Paid' THEN i.total_amount ELSE 0 END) AS total_received,
  ROUND(
    SUM(CASE WHEN i.status = 'Paid' THEN i.total_amount ELSE 0 END) * 100.0 
    / NULLIF(SUM(i.total_amount), 0), 
    2
  ) AS payment_conversion_rate
FROM 
  Client cl
JOIN Invoice i ON cl.client_id = i.client_id
GROUP BY 
  cl.client_id, cl.client_name
ORDER BY 
  payment_conversion_rate DESC;

6. Просечно Време до Наплата по Месеци

SELECT 
  TO_CHAR(i.payment_date, 'YYYY-MM') AS payment_month,
  AVG(i.payment_date - i.issue_date) AS avg_days_to_payment
FROM 
  Invoice i
WHERE 
  i.status = 'Paid'
GROUP BY 
  TO_CHAR(i.payment_date, 'YYYY-MM')
ORDER BY 
  payment_month;

7. Извештај за Фактури по Статус и Месец

SELECT 
  TO_CHAR(issue_date, 'YYYY-MM') AS month,
  status,
  COUNT(*) AS num_invoices,
  SUM(total_amount) AS total_amount
FROM 
  Invoice
GROUP BY 
  month, status
ORDER BY 
  month, status;

8. Месечен Извештај за Топ Клиенти по Приходи

SELECT 
  TO_CHAR(i.issue_date, 'YYYY-MM') AS month,
  cl.client_id,
  cl.client_name,
  SUM(i.total_amount) AS monthly_revenue
FROM 
  Invoice i
JOIN Client cl ON i.client_id = cl.client_id
GROUP BY 
  month, cl.client_id, cl.client_name
ORDER BY 
  month, monthly_revenue DESC;

9. Извештај за Фактури со Попуст по Месец и Клиент

SELECT 
  TO_CHAR(i.issue_date, 'YYYY-MM') AS month,
  cl.client_name,
  COUNT(*) AS num_discounted_invoices,
  SUM(i.total_amount * i.discount_percentage / 100.0) AS total_discount_value
FROM 
  Invoice i
JOIN Client cl ON i.client_id = cl.client_id
WHERE 
  i.discount_percentage > 0
GROUP BY 
  month, cl.client_name
ORDER BY 
  total_discount_value DESC;

10. Извештај за Просечна Вредност на Фактура по Компанија

SELECT 
  c.company_id,
  c.company_name,
  COUNT(i.invoice_id) AS total_invoices,
  ROUND(AVG(i.total_amount), 2) AS avg_invoice_value
FROM 
  Company c
JOIN Invoice i ON c.company_id = i.company_id
GROUP BY 
  c.company_id, c.company_name
ORDER BY 
  avg_invoice_value DESC;

11. Вкупна Наплата во Последните 12 Месеци по Клиент

SELECT 
    cl.client_name,
    SUM(p.amount) AS total_paid
FROM 
    Payment p
JOIN Invoice i ON p.invoice_id = i.invoice_id
JOIN Client cl ON i.client_id = cl.client_id
WHERE 
    p.payment_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 
    cl.client_name
ORDER BY 
    total_paid DESC;

12. Просечно време до плаќање

SELECT 
    AVG(i.payment_date - i.issue_date) AS avg_days_to_payment
FROM 
    Invoice i
WHERE 
    i.status = 'Paid';