Q:

How to calculate distance driven by day based on mileage

I am trying to calculate the distance driven based on the mileage. I am grabbing the MAX and MIN distance per day and subtract that from each other. However this does not use the 'last' mileage from the previous day, so a piece of 'distance' is missing.
How can I grab the 'latest' mileage before the given day and use that as the MIN mileage instead.
SQL Fiddle: http://www.sqlfiddle.com/#!7/3ea7d/7/0
PS: In this fiddle I use a simple 'day' stored as a integer, however in the real implementation this is a date stored as epoch which is then converted to day by strftime.
Desired results:
day      distance
1        300
2        400
3        400

A:

Assuming you want the previous day's maximum, you can use a correlated subquery:
WITH td as (
      SELECT t.day, MAX(t.distance) as maxd, MIN(t.distance) mind
      FROM trips t
      GROUP BY t.day
     )
SELECT td.day,
       (maxd -
        COALESCE( (SELECT MAX(td2.maxd)
                   FROM td td2
                   WHERE td2.day < td.day
                  ),
                  td.mind
                )
       ) as diff
FROM td;

Most databases support the ANSI standard LAG() function, which would make this much easier.  Alas, SQLite does not support it.

