Date Manipulation

Hard
3
52.1% 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's julianday function for this calculation. If there's no preceding invoice for a particular customer, this value should be null.

SQL Features

Utilize SQLite window functions like LAG(), OVER(), PARTITION BY.

Your query should order the result first by "CustomerId", and then by "InvoiceDate".