Measure invoice collection rate by year

#3 · 3 of 5Free sample

Exercises (5)

Instructions

Finance tracks how much billed revenue is collected. Exclude void invoices. For each invoice year, sum amount_due, sum amount_due on paid invoices, and calculate the collection rate.

Return four columns: year, billed, collected, and collection_rate_pct rounded to 1 decimal. Order by year.

Loading the practice editor and dataset…

Tables

5 tables · 28 columns

plans4 cols

plan_idTEXT PRIMARY KEY
plan_nameTEXT NOT NULL
monthly_priceREAL NOT NULL
included_seatsINTEGER NOT NULL

accounts5 cols

account_idTEXT PRIMARY KEY
created_atTEXT NOT NULL
regionTEXT NOT NULL
segmentTEXT NOT NULL
acquisition_channelTEXT NOT NULL

subscriptions8 cols

subscription_idTEXT PRIMARY KEY
account_idTEXT NOT NULL
plan_idTEXT NOT NULL
started_atTEXT NOT NULL
ended_atTEXT
statusTEXT NOT NULL
seatsINTEGER NOT NULL
mrrREAL NOT NULL

invoices6 cols

invoice_idTEXT PRIMARY KEY
subscription_idTEXT NOT NULL
invoice_dateTEXT NOT NULL
due_dateTEXT NOT NULL
amount_dueREAL NOT NULL
invoice_statusTEXT NOT NULL

payments5 cols

payment_idTEXT PRIMARY KEY
invoice_idTEXT NOT NULL
attempted_atTEXT NOT NULL
payment_statusTEXT 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 →