Lag() Function in SQLiteStudio

Question

I am wanting to return the last transaction date grouped by CustomerID, and I am using SQLiteStudio 3.2.1. My table looks like this:

CustomerID   Date       TransactionID    Amount
    1      2000-07-01          1          20.00
    2      2000-07-04          2          40.00
    1      2002-08-01          3          20.00
    1      2007-01-01          4          60.00
    2      2010-05-09          5          70.00
    1      2012-06-25          6          35.00`

This is what I would like the end result to look like: `

CustomerID       Date       TransactionID    Amount  Last Transaction Date
    1          2000-07-01          1          20.00           NULL
    2          2000-07-04          2          40.00           NULL
    1          2002-08-01          3          20.00        2000-07-01
    1          2007-01-01          4          60.00        2002-01-01 
    2          2010-05-09          5          70.00        2000-07-04
    1          2012-06-25          6          35.00`       2007-01-01

I was attempting to use the following code:

SELECT CustomerID, Date, Amount, LAG(Date,1) OVER (PARTITIONED BY CustomerID ORDER BY Date) 
FROM table

However, the lag function is not supported in SQLiteStudio (or maybe I am missing something?). The SQL Editor is also not recognizing the PARTITION BY clause either. Is there a way to use the LAG function or the PARTITION BY clause in the SQL Function Editor? Any help would be greatly appreciated! Thanks!

Also: does anyone have any resources for aggregate function creation in the SQL Function Editor for SQLiteStudio? I know it takes the three parameters of "Initialization code", "Per step code", and "Final step implementation code", but I am looking for examples of the syntax/requirements for these three parameters in SQLiteStudio. (Thanks again!)

score 0 · Accepted Answer · answered Jan 05 '21 at 03:34

Your partition clause, as your pasted above, has a typo, and it should be PARTITION BY, not PARTITIONED BY. If this be the only problem, then just fix the typo:

SELECT CustomerID, Date, Amount,
       LAG(Date) OVER (PARTITION BY CustomerID
                       ORDER BY Date) AS "Last Transaction Date"
FROM yourTable
ORDER BY Date;

If the above still does not work, then perhaps your version of SQLite does not support LAG. One workaround in this case would be to use a correlated subquery in place of LAG:

SELECT CustomerID, Date, Amount,
       (SELECT t2.Date
        FROM yourTable t2
        WHERE t2.CustomerID = t1.CustomerID AND
              t2.TransactionID < t1.TransactionID
        ORDER BY t2.TransactionID DESC
        LIMIT 1) AS "Last Transaction Date"
FROM yourTable t1
ORDER BY Date;

SQLiteStudio 3.2.1 comes with SQLite 3.24.0. You can replace SQLite library in SQLiteStudio's installation directory with more recent SQLite version. It will allow you to use all recent features in SQL queries, although be prepared that SQLiteStudio will underline them as syntax error - which is fine, you still can execute them. — Googie, Jan 05 '21 at 22:08

Lag() Function in SQLiteStudio

1 Answers1