Lead / Lag function Bug?

Help Procedural SQL
1

I am using a lead function to check if the price of previous day has changed or not.
The price is grouped by event_date, event_id, and ticket_id.
The first row is correct, yesterday’s price does not exist, however for ticket_id (l_6EQyM3NiKN51NdHLVw9m1SGyvD4Pf1dAlXjC9QmAN3cPdeKnTEM8QbWcx6u6rC5p) the price does not exist either and Null should be returned instead of 59.

Am I doing something wrong with the query?

WITH dailyticketprice AS 
    (SELECT date(TIME_BUCKET('1d')) event_date, event_id,ticket_id, LAST(price) AS ticket_price
    FROM ticket_analysis_daily
    WHERE ticket_id IN('l_048N53QfNvpnvZcN4j0pXFG0Lybpf6ZvYWbS6ne91dC3zYEPMF4pmdYzu4qiQpimr', 'l_6EQyM3NiKN51NdHLVw9m1SGyvD4Pf1dAlXjC9QmAN3cPdeKnTEM8QbWcx6u6rC5p')
    GROUP BY  1,2,3
    ORDER BY  event_id,ticket_id,event_date ), 
        price_variation AS 
    (SELECT event_date,
         event_id,
         ticket_id,
         ticket_price,
        lag(ticket_price) OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) AS yesterday_price, 
        
        ticket_price - lag(ticket_price) OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) AS price_diff, 
        (case
        WHEN ticket_price - lag(ticket_price)
        OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) > 0 THEN
        'Up'
        WHEN ticket_price - lag(ticket_price)
        OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) < 0 THEN
        'Down'
        WHEN ticket_price - lag(ticket_price)
        OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) = 0 THEN
        'Unchanged'
        WHEN ticket_price - lag(ticket_price)
        OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) is NULL THEN
        "New" 
        end) AS price_status
    FROM dailyticketprice )
SELECT event_date,
         event_id,
         ticket_id,
         ticket_price,
         yesterday_price,
           price_status
FROM price_variation;

Screen Shot 2022-08-26 at 10.02.05 AM
Screen Shot 2022-08-26 at 10.02.05 AM1920×1340 383 KB

2

Can you give the schema of your table (ticket_analysis_daily) and a few snapshots of these tables so I can have a few inserts and repro your query?

3

@arnaud Sure.

You can download here.

4

Here is the syntax: you were missing the over partition clause to let S2 know when to restart looking at the prior value. Let me know if that works.

WITH dailyticketprice AS 
    (SELECT date(TIME_BUCKET('1d')) event_date, event_id,ticket_id, LAST(price) AS ticket_price
    FROM ticket_analysis_daily
    WHERE ticket_id IN('l_048N53QfNvpnvZcN4j0pXFG0Lybpf6ZvYWbS6ne91dC3zYEPMF4pmdYzu4qiQpimr', 'l_6EQyM3NiKN51NdHLVw9m1SGyvD4Pf1dAlXjC9QmAN3cPdeKnTEM8QbWcx6u6rC5p')
    GROUP BY  1,2,3
    ORDER BY  event_id,ticket_id,event_date ), 
        price_variation AS 
    (SELECT event_date,
         event_id,
         ticket_id,
         ticket_price,
        lag(ticket_price, 1) OVER (PARTITION BY (ticket_id) 
                                    order by (event_id) ASC, (ticket_id) ASC,(event_date) ASC) AS yesterday_price, 
        ticket_price - lag(ticket_price) OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) AS price_diff, 
        (case
        WHEN ticket_price - lag(ticket_price)
        OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) > 0 THEN
        'Up'
        WHEN ticket_price - lag(ticket_price)
        OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) < 0 THEN
        'Down'
        WHEN ticket_price - lag(ticket_price)
        OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) = 0 THEN
        'Unchanged'
        WHEN ticket_price - lag(ticket_price)
        OVER (order by (event_id) ASC,(ticket_id)ASC,(event_date) ASC) is NULL THEN
        "New" 
        end) AS price_status
    FROM dailyticketprice )
SELECT event_date,
         event_id,
         ticket_id,
         ticket_price,
         yesterday_price,
           price_status
FROM price_variation;
1 Like
5

Awesome. Thank you @arnaud . That fixed it.

1 Like