SQL - Count comparison of two snapshots on the same table-CodePudding

I have a table with daily offers for products, where OFFERING_ID is the primary key and there is also PRODUCT_ID. One PRODUCT_ID can have one or multiple OFFERING_ID. I am trying to compare prices for OFFERING_ID in two SNAPSHOT_DAY (one month between two dates). To make things more tricky, there might be OFFERING_ID present in both dates, or just in the earlier date (has been sold) or just in the later date (new listings); I want to compare only cases where there are OFFERING_ID in both dates, so basically I want to ignore any date pairs with a NULL.

My end goal is to get a count of the number of OFFERING_ID where 1) The price didn't change between dates, 2) Price went down, 3) Price went up.

I've tried creating a query to check for the price before (BEFORE), another one to check for the price after (AFTER), and then a last one with this:

SELECT
    COUNT(CASE WHEN BEFORE.PRICE_BEFORE IS NOT NULL AND AFTER.PRICE_AFTER IS NOT NULL AND
          BEFORE.PRICE_BEFORE = AFTER.PRICE_AFTER THEN 1 ELSE 0 END) AS SAME_PRICE
    ,COUNT(CASE WHEN BEFORE.PRICE_BEFORE IS NOT NULL AND AFTER.PRICE_AFTER IS NOT NULL AND
          BEFORE.PRICE_BEFORE > AFTER.PRICE_AFTER THEN 1 ELSE 0 END) AS LOWER_PRICE      
    ,COUNT(CASE WHEN BEFORE.PRICE_BEFORE IS NOT NULL AND AFTER.PRICE_AFTER IS NOT NULL AND
          BEFORE.PRICE_BEFORE < AFTER.PRICE_AFTER THEN 1 ELSE 0 END) AS HIGHER_PRICE 

WHERE
     AFTER INNER JOIN BEFORE ON AFTER.OFFERING_ID = BEFORE.OFFERING_ID AND AFTER.PRODUCT_ID = BEFORE.PRODUCT_ID

I ran it for just one PRODUCT_ID and I get completely wrong results that say SAME_PRICE = 24, LOWER_PRICE = 24 and HIGHER_PRICE = 24. This is completely wrong as there aren't even 24 OFFERING_ID with both dates for those two products.

I am using PostgreSQL.

CodePudding user response：

SELECT 
  SUM(CASE WHEN BEFORE.PRICE = AFTER.PRICE THEN 1 ELSE 0 END) AS SAME_PRICE,
  SUM(CASE WHEN BEFORE.PRICE > AFTER.PRICE THEN 1 ELSE 0 END) AS LOWER_PRICE,
  SUM(CASE WHEN BEFORE.PRICE < AFTER.PRICE THEN 1 ELSE 0 END) AS HIGHER_PRICE
FROM
  AFTER 
  JOIN BEFORE ON AFTER.OFFERING_ID = BEFORE.OFFERING_ID AND AFTER.PRODUCT_ID = BEFORE.PRODUCT_ID

You don't need checking if price is null because if it is - it will be eliminated by JOIN.