CODEWITHSUNDEEP
sql
Sloth87
Sloth87

Reputation: 785

Filtering a SQL query

I'm struggling with a SQL query to show the latest prices with the previous price and the different. I have the below code which shows a price with the previous price using LAG.

SELECT
     t.site_id As "Site",
     t.type As "Product",
     t.recorded_time As "Recorded Date",
     t.price / 10 As "Price",
     LAG(t.price) OVER(PARTITION BY t.site_id , t.type  ORDER BY t.recorded_time ) / 10 previous_price,
     LAG(t.recorded_time) OVER(PARTITION BY t.site_id , t.type ORDER BY t.recorded_time ) previous_date,
     (t.price -LAG(t.price) OVER(PARTITION BY t.site_id , t.type  ORDER BY t.recorded_time )) / 10 As "Pricing change"
FROM  table t

I have tried adding the below join to just show the records that have the maximum date. However, the three additional columns previous_price, previous_date and Price change are all empty.

INNER JOIN( SELECT
         site_id,
         max(recorded_time) as MaxDate,
         type
FROM  table 
GROUP BY site_id,
      type 
) tm ON t.site_id  = tm.site_id
 AND    t.recorded_time  = tm.MaxDate
 AND    t.type  = tm.type

Any help would be greatly appreciated

Upvotes: 0

Views: 28

Answers (1)

Kurt
Kurt

Reputation: 1748

I think you want to put your original query in a subquery, and then join that subquery to the tm subquery, so the LAG will be calculated prior to the join instead of after, something like this:

SELECT
    t.*
FROM (
        SELECT
            t.site_id,
            t.type,
            t.recorded_time,
            t.price / 10 As price,
            LAG(t.price) OVER (PARTITION BY t.site_id, t.type ORDER BY t.recorded_time) / 10 previous_price,
            LAG(t.recorded_time) OVER (PARTITION BY t.site_id, t.type ORDER BY t.recorded_time) previous_date,
            (t.price - LAG(t.price) OVER (PARTITION BY t.site_id,t.type ORDER BY t.recorded_time)) / 10 As pricing_change
        FROM
            table
    ) t
    INNER JOIN (
        SELECT
            site_id,
            max(recorded_time) as MaxDate,
            type
        FROM
            table
        GROUP BY
            site_id,
            type
    ) tm 
        ON t.site_id = tm.site_id
        AND t.recorded_time = tm.MaxDate
        AND t.type = tm.type

Upvotes: 1

Related Questions