GST Split Into CGST, SGST and IGST by Seller — SQL Problem
Under GST, a sale within the seller's own state is taxed as CGST plus SGST, each half of the GST rate; a sale to another state is taxed as IGST at the full…
- Difficulty: Medium
- Topics: Conditional Logic, Joins, Dates
- Dialect: MySQL
- Problem: #114
Problem statement
Under GST, a sale within the seller's own state is taxed as CGST plus SGST, each half of the GST rate; a sale to another state is taxed as IGST at the full rate. Tax on an invoice is taxable_value * gst_rate / 100 in total.
For every seller and every month in which they invoiced, return seller_id, month (YYYY-MM), cgst, sgst and igst — the tax of each kind over that month's invoices, each rounded to 2 decimals (0 when there is none of that kind). Order by seller_id, then month.
Tables
Table: sellers
| Column | Type |
|---|---|
| seller_id | int |
| store_name | varchar |
| state | varchar |
Primary key: seller_id.
One row per seller; state is where the seller is registered for GST.
Table: gst_invoices
| Column | Type |
|---|---|
| invoice_id | int |
| seller_id | int |
| invoice_date | date |
| buyer_state | varchar |
| taxable_value | int |
| gst_rate | int |
Primary key: invoice_id.
One row per tax invoice; taxable_value in rupees before tax, gst_rate in percent (5, 12, 18 or 28).
Examples
Example 1
sellers
| seller_id | store_name | state |
|---|---|---|
| 1 | Mehta Electronics | Maharashtra |
| 2 | Lakshmi Textiles | Tamil Nadu |
gst_invoices
| invoice_id | seller_id | invoice_date | buyer_state | taxable_value | gst_rate |
|---|---|---|---|---|---|
| 1 | 1 | 2024-07-03 | Maharashtra | 10000 | 18 |
| 2 | 1 | 2024-07-09 | Karnataka | 25000 | 18 |
| 3 | 1 | 2024-07-21 | Maharashtra | 1500 | 28 |
| 4 | 1 | 2024-08-02 | Gujarat | 4200 | 12 |
| 5 | 2 | 2024-07-11 | Tamil Nadu | 3300 | 5 |
| 6 | 2 | 2024-07-30 | Tamil Nadu | 2100 | 5 |
| 7 | 2 | 2024-08-18 | Kerala | 8000 | 5 |
Output
| seller_id | month | cgst | sgst | igst |
|---|---|---|---|---|
| 1 | 2024-07 | 1110 | 1110 | 4500 |
| 1 | 2024-08 | 0 | 0 | 504 |
| 2 | 2024-07 | 135 | 135 | 0 |
| 2 | 2024-08 | 0 | 0 | 400 |
How to solve GST Split Into CGST, SGST and IGST by Seller
The kind of tax is decided per invoice by comparing two states: the buyer's, on the invoice, and the seller's, in sellers. So join the two tables, then do conditional aggregation per seller and month — three SUMs over CASE expressions, each adding an invoice's tax when it is of that kind and 0 otherwise. The ELSE 0 is what makes a month with only inter-state sales show cgst and sgst as 0 rather than NULL.
For an intra-state invoice the GST is split evenly: CGST and SGST are each taxable_value * gst_rate / 200. For inter-state the whole taxable_value * gst_rate / 100 is IGST. Invoice 1 in the example (₹10,000 at 18% within Maharashtra) contributes ₹900 CGST and ₹900 SGST; invoice 2 to Karnataka contributes ₹4,500 IGST.
The month key is DATE_FORMAT(invoice_date, '%Y-%m'), and grouping by the same expression keeps ONLY_FULL_GROUP_BY happy. Rounding to 2 decimals makes the output identical in every engine. The alternative computes intra- and inter-state tax per row in a derived table and halves the intra total afterwards — the same arithmetic in a different order.
Reference solution (MySQL)
SELECT i.seller_id, DATE_FORMAT(i.invoice_date, '%Y-%m') AS month,
ROUND(SUM(CASE WHEN i.buyer_state = s.state THEN i.taxable_value * i.gst_rate / 200 ELSE 0 END), 2) AS cgst,
ROUND(SUM(CASE WHEN i.buyer_state = s.state THEN i.taxable_value * i.gst_rate / 200 ELSE 0 END), 2) AS sgst,
ROUND(SUM(CASE WHEN i.buyer_state <> s.state THEN i.taxable_value * i.gst_rate / 100 ELSE 0 END), 2) AS igst
FROM gst_invoices i
JOIN sellers s ON s.seller_id = i.seller_id
GROUP BY i.seller_id, DATE_FORMAT(i.invoice_date, '%Y-%m')
ORDER BY i.seller_id, monthAnother way
SELECT seller_id, month, ROUND(SUM(intra) / 2, 2) AS cgst, ROUND(SUM(intra) / 2, 2) AS sgst, ROUND(SUM(inter), 2) AS igst
FROM (
SELECT i.seller_id, LEFT(i.invoice_date, 7) AS month,
IF(i.buyer_state = s.state, i.taxable_value * i.gst_rate / 100, 0) AS intra,
IF(i.buyer_state = s.state, 0, i.taxable_value * i.gst_rate / 100) AS inter
FROM gst_invoices i JOIN sellers s ON s.seller_id = i.seller_id
) t
GROUP BY seller_id, month
ORDER BY seller_id, month← Customers Who Reordered Within 30 Days · Wishlisted Products That Are Now Cheaper →