Advanced SQL Queries and Reports for Invoices

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

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;

2. Извештај за Клиентски Перформанси – Побарани 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;

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

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;

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

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;

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

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;

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

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;

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

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;

8. Вкупна Наплата во Последните 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;

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

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