Date Manipulation
Hard
3
50.7% Acceptance
Your task is to calculate the difference in days between each invoice and the invoice that precedes it, for each customer.
Final Columns
CustomerId
: The ID of the customer from the "Invoice" table.InvoiceDate
: The date of the invoice from the "Invoice" table.DaysSinceLastInvoice
: The difference in days between the current "InvoiceDate" and the preceding "InvoiceDate" for the same customer. Use SQLite'sjulianday
function for this calculation. If there's no preceding invoice for a particular customer, this value should benull
.
SQL Features
Utilize SQLite window functions like LAG()
, OVER()
, PARTITION BY
.
Your query should order the result first by "CustomerId"
, and then by "InvoiceDate"
.