Changes between Version 4 and Version 5 of SQL


Ignore:
Timestamp:
08/28/25 12:08:36 (11 days ago)
Author:
163080
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • SQL

    v4 v5  
    11== Advanced SQL Queries and Reports for Invoices
    22
    3 === 1. Report: Total Revenue Generated by Each Company in the Last Year
     3=== 1. Годишен Извештај за Приходи по Месеци (за секоја компанија)
    44
    55{{{
    66SELECT
    7     c.company_id,
    8     c.company_name,
    9     SUM(i.total_amount) AS total_revenue
     7  c.company_id,
     8  c.company_name,
     9  TO_CHAR(i.issue_date, 'YYYY-MM') AS month,
     10  COUNT(i.invoice_id) AS num_invoices,
     11  SUM(i.total_amount) AS monthly_revenue
    1012FROM
    11     Company c
    12 JOIN Invoice i ON c.company_id = i.company_id
    13 WHERE
    14     i.issue_date BETWEEN NOW() - INTERVAL '1 year' AND NOW()
     13  Invoice i
     14JOIN Company c ON c.company_id = i.company_id
    1515GROUP BY
    16     c.company_id, c.company_name
     16  c.company_id, c.company_name, TO_CHAR(i.issue_date, 'YYYY-MM')
    1717ORDER BY
    18     total_revenue DESC;
     18  c.company_name, month;
     19
    1920}}}
    2021
    21 === 2. Report: Invoices Issued to Each Client and Their Total Amount
     22=== 2. Извештај за Клиентски Перформанси – Побарани vs Добиени Средства
    2223{{{
    2324SELECT
    24     cl.client_id,
    25     cl.client_name,
    26     COUNT(i.invoice_id) AS total_invoices,
    27     SUM(i.total_amount) AS total_amount
     25  cl.client_id,
     26  cl.client_name,
     27  COUNT(i.invoice_id) AS total_invoices,
     28  SUM(i.total_amount) AS total_requested,
     29  SUM(CASE WHEN i.status = 'Paid' THEN i.total_amount ELSE 0 END) AS total_received,
     30  ROUND(
     31    SUM(CASE WHEN i.status = 'Paid' THEN i.total_amount ELSE 0 END) * 100.0
     32    / NULLIF(SUM(i.total_amount), 0),
     33    2
     34  ) AS payment_conversion_rate
    2835FROM
    29     Client cl
     36  Client cl
    3037JOIN Invoice i ON cl.client_id = i.client_id
    3138GROUP BY
    32     cl.client_id, cl.client_name
     39  cl.client_id, cl.client_name
    3340ORDER BY
    34     total_amount DESC;
     41  payment_conversion_rate DESC;
     42
    3543}}}
    3644
    37 === 3. Report: List of Overdue Invoices
     45=== 3. Просечно Време до Наплата по Месеци
    3846{{{
    3947SELECT
    40     i.invoice_id,
    41     i.issue_date,
    42     i.due_date,
    43     i.total_amount,
    44     cl.name AS client_name,
    45     c.name AS company_name
     48  TO_CHAR(i.payment_date, 'YYYY-MM') AS payment_month,
     49  AVG(i.payment_date - i.issue_date) AS avg_days_to_payment
    4650FROM
    47     Invoice i
    48 JOIN Client cl ON i.client_id = cl.client_id
    49 JOIN Company c ON i.company_id = c.company_id
     51  Invoice i
    5052WHERE
    51     i.due_date < NOW() AND i.status != 'Paid'
     53  i.status = 'Paid'
     54GROUP BY
     55  TO_CHAR(i.payment_date, 'YYYY-MM')
    5256ORDER BY
    53     i.due_date ASC;
     57  payment_month;
     58
    5459}}}
    5560
    56 === 4. Report: Clients with the Highest Invoice Payments
     61=== 4. Извештај за Фактури по Статус и Месец
    5762{{{
    5863SELECT
    59     cl.client_id,
    60     cl.client_name,
    61     SUM(i.total_amount) AS total_paid
     64  TO_CHAR(issue_date, 'YYYY-MM') AS month,
     65  status,
     66  COUNT(*) AS num_invoices,
     67  SUM(total_amount) AS total_amount
    6268FROM
    63     Client cl
    64 JOIN Invoice i ON cl.client_id = i.client_id
    65 WHERE
    66     i.status = 'Paid'
     69  Invoice
    6770GROUP BY
    68     cl.client_id, cl.client_name
     71  month, status
    6972ORDER BY
    70     total_paid DESC
    71 LIMIT 10;
     73  month, status;
     74
    7275}}}
    7376
    74 === 5. Report: Outstanding Invoices by Each Client
     77=== 5. Месечен Извештај за Топ Клиенти по Приходи
    7578{{{
    7679SELECT
    77     cl.client_id,
    78     cl.client_name,
    79     COUNT(i.invoice_id) AS outstanding_invoices,
    80     SUM(i.total_amount) AS outstanding_amount
     80  TO_CHAR(i.issue_date, 'YYYY-MM') AS month,
     81  cl.client_id,
     82  cl.client_name,
     83  SUM(i.total_amount) AS monthly_revenue
    8184FROM
    82     Client cl
    83 JOIN Invoice i ON cl.client_id = i.client_id
    84 WHERE
    85     i.status = 'Unpaid'
     85  Invoice i
     86JOIN Client cl ON i.client_id = cl.client_id
    8687GROUP BY
    87     cl.client_id, cl.client_name
     88  month, cl.client_id, cl.client_name
    8889ORDER BY
    89     outstanding_amount DESC;
     90  month, monthly_revenue DESC;
     91
    9092}}}
    9193
    92 === 6. Report: Monthly Invoice Revenue
     94=== 6. Извештај за Фактури со Попуст по Месец и Клиент
    9395{{{
    9496SELECT
    95     TO_CHAR(i.issue_date, 'YYYY-MM') AS month,
    96     SUM(i.total_amount) AS total_revenue
     97  TO_CHAR(i.issue_date, 'YYYY-MM') AS month,
     98  cl.client_name,
     99  COUNT(*) AS num_discounted_invoices,
     100  SUM(i.total_amount * i.discount_percentage / 100.0) AS total_discount_value
    97101FROM
    98     Invoice i
     102  Invoice i
     103JOIN Client cl ON i.client_id = cl.client_id
     104WHERE
     105  i.discount_percentage > 0
    99106GROUP BY
    100     TO_CHAR(i.issue_date, 'YYYY-MM')
     107  month, cl.client_name
    101108ORDER BY
    102     month DESC;
     109  total_discount_value DESC;
     110
    103111}}}
    104112
    105 == 7. Report: Top 5 Products/Services Sold by Revenue
     113== 7. Извештај за Просечна Вредност на Фактура по Компанија
    106114{{{
    107115SELECT
    108     l.description AS product_name,
    109     SUM(l.quantity * l.unit_price) AS total_revenue
     116  c.company_id,
     117  c.company_name,
     118  COUNT(i.invoice_id) AS total_invoices,
     119  ROUND(AVG(i.total_amount), 2) AS avg_invoice_value
    110120FROM
    111     LineItem l
     121  Company c
     122JOIN Invoice i ON c.company_id = i.company_id
    112123GROUP BY
    113     l.description
     124  c.company_id, c.company_name
    114125ORDER BY
    115     total_revenue DESC
    116 LIMIT 5;
     126  avg_invoice_value DESC;
     127
    117128}}}
    118129
    119 === 8. Report: Invoices with the Most Line Items
     130=== 8. Вкупна Наплата во Последните 12 Месеци по Клиент
    120131{{{
    121132SELECT
    122     i.invoice_id,
    123     i.issue_date,
    124     i.total_amount,
    125     COUNT(l.line_item_id) AS line_item_count
     133    cl.client_name,
     134    SUM(p.amount) AS total_paid
    126135FROM
    127     Invoice i
    128 JOIN LineItem l ON i.invoice_id = l.invoice_id
     136    Payment p
     137JOIN Invoice i ON p.invoice_id = i.invoice_id
     138JOIN Client cl ON i.client_id = cl.client_id
     139WHERE
     140    p.payment_date >= CURRENT_DATE - INTERVAL '12 months'
    129141GROUP BY
    130     i.invoice_id, i.issue_date, i.total_amount
     142    cl.client_name
    131143ORDER BY
    132     line_item_count DESC
    133 LIMIT 10;
     144    total_paid DESC;
     145
    134146}}}
    135147
    136 == 9. Report: Average Time to Payment
     148== 9. Просечно време до плаќање
    137149{{{
    138150SELECT
    … …  
    143155    i.status = 'Paid';
    144156}}}
    145 
    146 === 10. Report: Invoices with Discounts Applied
    147 {{{
    148 SELECT
    149     i.invoice_id,
    150     i.issue_date,
    151     i.total_amount,
    152     i.discount_percentage,
    153     (i.total_amount * i.discount_percentage / 100) AS discount_amount
    154 FROM
    155     Invoice i
    156 WHERE
    157     i.discount_percentage > 0
    158 ORDER BY
    159     discount_amount DESC;
    160 }}}