CODEWITHSUNDEEP
sqlsqlite
statstudent
statstudent

Reputation: 45

finding the number of days in between first 2 date point

So the question seems to be quite difficult I wonder if I could get some advice from here. I am trying to solve this with SQLite 3. So I have a data format of this.

customer | purchase date
1        | date 1
1        | date 2
1        | date 3
2        | date 4
2        | date 5
2        | date 6
2        | date 7

number of times the customer repeats is random.

so I just want to find whether customer 1's 1st and 2nd purchase date are fallen in between a specific time period. repeat for other customers. only need to consider 1st and 2nd dates. Any help would be appreciated!

Upvotes: 2

Views: 52

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522226

We can try using ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY "purchase date") rn
    FROM yourTable
)

SELECT
    customer,
    CAST(MAX(CASE WHEN rn = 2 THEN julianday("purchase date") END) -
         MAX(CASE WHEN rn = 1 THEN julianday("purchase date") END) AS INTEGER) AS diff_in_days
FROM cte
GROUP BY
    customer;

The idea here is to aggregate by customer and then take the date difference between the second and first purchase. ROW_NUMBER is used to find these first and second purchases, for each customer.

Upvotes: 1

Related Questions