Advanced SQL Queries and Reports for Invoices

1. Report: Total Revenue Generated by Each Company in the Last Year

SELECT 
    c.company_id,
    c.name AS company_name,
    SUM(i.total_amount) AS total_revenue
FROM 
    Company c
JOIN Invoice i ON c.company_id = i.company_id
WHERE 
    i.issue_date BETWEEN NOW() - INTERVAL '1 year' AND NOW()
GROUP BY 
    c.company_id, c.name
ORDER BY 
    total_revenue DESC;

2. Report: Invoices Issued to Each Client and Their Total Amount

SELECT 
    cl.client_id,
    cl.name AS client_name,
    COUNT(i.invoice_id) AS total_invoices,
    SUM(i.total_amount) AS total_amount
FROM 
    Client cl
JOIN Invoice i ON cl.client_id = i.client_id
GROUP BY 
    cl.client_id, cl.name
ORDER BY 
    total_amount DESC;

3. Report: List of Overdue Invoices

SELECT 
    i.invoice_id,
    i.issue_date,
    i.due_date,
    i.total_amount,
    cl.name AS client_name,
    c.name AS company_name
FROM 
    Invoice i
JOIN Client cl ON i.client_id = cl.client_id
JOIN Company c ON i.company_id = c.company_id
WHERE 
    i.due_date < NOW() AND i.status != 'Paid'
ORDER BY 
    i.due_date ASC;

4. Report: Clients with the Highest Invoice Payments

SELECT 
    cl.client_id,
    cl.name AS client_name,
    SUM(i.total_amount) AS total_paid
FROM 
    Client cl
JOIN Invoice i ON cl.client_id = i.client_id
WHERE 
    i.status = 'Paid'
GROUP BY 
    cl.client_id, cl.name
ORDER BY 
    total_paid DESC
LIMIT 10;

5. Report: Outstanding Invoices by Each Client

SELECT 
    cl.client_id,
    cl.name AS client_name,
    COUNT(i.invoice_id) AS outstanding_invoices,
    SUM(i.total_amount) AS outstanding_amount
FROM 
    Client cl
JOIN Invoice i ON cl.client_id = i.client_id
WHERE 
    i.status = 'Unpaid'
GROUP BY 
    cl.client_id, cl.name
ORDER BY 
    outstanding_amount DESC;

6. Report: Monthly Invoice Revenue

SELECT 
    TO_CHAR(i.issue_date, 'YYYY-MM') AS month,
    SUM(i.total_amount) AS total_revenue
FROM 
    Invoice i
GROUP BY 
    TO_CHAR(i.issue_date, 'YYYY-MM')
ORDER BY 
    month DESC;

7. Report: Top 5 Products/Services Sold by Revenue

SELECT 
    l.description AS product_name,
    SUM(l.quantity * l.unit_price) AS total_revenue
FROM 
    LineItem l
GROUP BY 
    l.description
ORDER BY 
    total_revenue DESC
LIMIT 5;

8. Report: Invoices with the Most Line Items

SELECT 
    i.invoice_id,
    i.issue_date,
    i.total_amount,
    COUNT(l.line_item_id) AS line_item_count
FROM 
    Invoice i
JOIN LineItem l ON i.invoice_id = l.invoice_id
GROUP BY 
    i.invoice_id, i.issue_date, i.total_amount
ORDER BY 
    line_item_count DESC
LIMIT 10;

9. Report: Average Time to Payment

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

10. Report: Invoices with Discounts Applied

SELECT 
    i.invoice_id,
    i.issue_date,
    i.total_amount,
    i.discount_percentage,
    (i.total_amount * i.discount_percentage / 100) AS discount_amount
FROM 
    Invoice i
WHERE 
    i.discount_percentage > 0
ORDER BY 
    discount_amount DESC;