Changes between Initial Version and Version 1 of SQL

Timestamp:

01/21/25 22:39:47 (9 months ago)

Author:

163080

Comment:

--

SQL

@@ +1 @@
+===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;
+}}}