Calculate net payments by year

#3 · 3 of 5Free sample

Exercises (5)

Instructions

Finance reports cash, not orders. For each calendar year, calculate successful payment amounts (payment_status = succeeded) by the year of paid_at, and refunds by the year of refunded_at. Net payments equal minus .

payments

refunds

Return four columns: year, payments, refunds, and net_payments, each amount rounded to 2 decimals. Order by year.

Loading the practice editor and dataset…

Tables

6 tables · 39 columns

customers5 cols

customer_idTEXT PRIMARY KEY
created_atTEXT NOT NULL
country_codeTEXT NOT NULL
acquisition_channelTEXT NOT NULL
customer_segmentTEXT NOT NULL

products6 cols

product_idTEXT PRIMARY KEY
skuTEXT NOT NULL
product_nameTEXT NOT NULL
categoryTEXT NOT NULL
unit_costREAL NOT NULL
list_priceREAL NOT NULL

orders9 cols

order_idTEXT PRIMARY KEY
customer_idTEXT NOT NULL
ordered_atTEXT NOT NULL
order_statusTEXT NOT NULL
currencyTEXT NOT NULL
subtotalREAL NOT NULL
tax_amountREAL NOT NULL
shipping_amountREAL NOT NULL
order_totalREAL NOT NULL

order_items7 cols

order_item_idTEXT PRIMARY KEY
order_idTEXT NOT NULL
product_idTEXT NOT NULL
quantityINTEGER NOT NULL
unit_priceREAL NOT NULL
discount_amountREAL NOT NULL
line_totalREAL NOT NULL

payments6 cols

payment_idTEXT PRIMARY KEY
order_idTEXT NOT NULL
paid_atTEXT NOT NULL
payment_methodTEXT NOT NULL
payment_statusTEXT NOT NULL
amountREAL NOT NULL

refunds6 cols

refund_idTEXT PRIMARY KEY
payment_idTEXT NOT NULL
order_idTEXT NOT NULL
refunded_atTEXT NOT NULL
refund_reasonTEXT NOT NULL
amountREAL NOT NULL

Sign up to unlock

The reference answer is one attempt away.

Create a free account to save your progress and reveal the worked answer for this free exercise. The complete practice library is available with Practice Pass or Mastery.

Save attempts and completed exercises
Reveal this free exercise's answer after an attempt
Return to your progress from any device
Free forever — no credit card

Create free account →Already a member? Log in →