Working with window function in SQLITE

Question

I have this set of data

purchasingid          date   supplierid
1                2014-01-01    12
2                2014-01-01    13
3                2013-12-06    12
4                2013-12-05    11
5                2014-01-01    17
6                2013-12-05    12

I want to check for all suppliers who bought on 2014-01-01 what was the date of their previous order. If it doesn't exists leave it blank.

meaning I want to get:

supplierid   date   last_time_buy_date
12            2014-01-01   2013-12-06
13            2014-01-01  
17            2014-01-01

supplierid 11 did not buy on 2014-01-01 so he does not appear at all.

This is what I did:

select supplierid,date, max(date)
from purchasing 
where supplierid in (select supplierid
                     from purchasing
                     where date='2014-01-01')

This doesnt work. I know I should use window function somehow but I don't know how to... Any thoughts?

Answer 1

SQLite doesn't support window functions. Instead, you can do:

select p.*,
       (select max(p2.date)
        from purchasing p2
        where p2.supplierid = p.supplierid and
              p2.date < p.date
       ) as prev_date
from purchasing p
where p.date = '2014-01-01';

Answer 2

SQLite will probably support window functions starting with version 3.25. The documentation for that is at https://www.sqlite.org/draft/windowfunctions.html.