GST Split Into CGST, SGST and IGST by Seller

Medium
114.

GST Split Into CGST, SGST and IGST by Seller

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

ColumnType
seller_idint
store_namevarchar
statevarchar

Primary key: seller_id.

One row per seller; state is where the seller is registered for GST.

Table: gst_invoices

ColumnType
invoice_idint
seller_idint
invoice_datedate
buyer_statevarchar
taxable_valueint
gst_rateint

Primary key: invoice_id.

One row per tax invoice; taxable_value in rupees before tax, gst_rate in percent (5, 12, 18 or 28).

Example

The rows must come back in the order shown.

sellers

seller_idstore_namestate
1Mehta ElectronicsMaharashtra
2Lakshmi TextilesTamil Nadu

gst_invoices

invoice_idseller_idinvoice_datebuyer_statetaxable_valuegst_rate
112024-07-03Maharashtra1000018
212024-07-09Karnataka2500018
312024-07-21Maharashtra150028
412024-08-02Gujarat420012
522024-07-11Tamil Nadu33005
622024-07-30Tamil Nadu21005
722024-08-18Kerala80005

Output

seller_idmonthcgstsgstigst
12024-07111011104500
12024-0800504
22024-071351350
22024-0800400
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
seller_idint
store_namevarchar
statevarchar

Primary key: seller_id.

One row per seller; state is where the seller is registered for GST.

Table: gst_invoices

ColumnType
invoice_idint
seller_idint
invoice_datedate
buyer_statevarchar
taxable_valueint
gst_rateint

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_idstore_namestate
1Mehta ElectronicsMaharashtra
2Lakshmi TextilesTamil Nadu

gst_invoices

invoice_idseller_idinvoice_datebuyer_statetaxable_valuegst_rate
112024-07-03Maharashtra1000018
212024-07-09Karnataka2500018
312024-07-21Maharashtra150028
412024-08-02Gujarat420012
522024-07-11Tamil Nadu33005
622024-07-30Tamil Nadu21005
722024-08-18Kerala80005

Output

seller_idmonthcgstsgstigst
12024-07111011104500
12024-0800504
22024-071351350
22024-0800400

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, month

Another 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 →