PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL for returning number of days since last availability, grouped by category?

I’m using SQL and I have some dummy car dealership data that for a given dealership tells me whether a particular make is currently being sold there, and if not, when the last time that make was available at that dealership. An example of a few rows of data for one dealership looks like this, focusing only on the fields of interest:

dealership_ID make Available? brought_in_date sold_date
612 BMW Yes 2024-11-23 NULL
612 BMW No 2024-09-13 2024-12-05
612 Audi No 2024-10-15 2024-10-28
612 Audi No 2024-09-06 2024-11-03
612 Mercedes Benz Yes 2024-10-20 NULL

What I’m trying to do is return one row per dealership that tells me for given car makes, whether they are currently available and if not, how many days has it been since they were last available

using dealership ID 612 as an example, it would return something like this:

dealership_ID BMW Audi Mercedes Benz Ford
612 0 61 0 NULL

Where BMW and Mercedes are 0 since at least one of each is available right now, Audi is 61 as it has been that many days since an Audi was last available at the dealership (audi with most recent sold_date) and Ford is Null as a ford has never been sold at this particular dealership before.

Tags:

2

Answers


  1. 0

    Well you can always simplify things and just use the with clause as in:

    WITH LatestAvailability AS (
    SELECT 
        dealership_ID,
        make,
        MAX(CASE WHEN Available = 'Yes' THEN 1 ELSE 0 END) AS is_available,
        MAX(sold_date) AS last_sold_date
    FROM dealership_data
    GROUP BY dealership_ID, make
),
DaysSinceLastAvailable AS (
    SELECT
        dealership_ID,
        make,
        CASE
            WHEN is_available = 1 THEN 0
            WHEN last_sold_date IS NOT NULL THEN DATEDIFF(DAY, last_sold_date, GETDATE())
            ELSE NULL
        END AS days_since_last_available
    FROM LatestAvailability
),
PivotedData AS (
    SELECT
        dealership_ID,
        make,
        days_since_last_available
    FROM DaysSinceLastAvailable
)
SELECT
    pd.dealership_ID,
    MAX(CASE WHEN pd.make = 'BMW' THEN pd.days_since_last_available ELSE NULL END) AS BMW,
    MAX(CASE WHEN pd.make = 'Audi' THEN pd.days_since_last_available ELSE NULL END) AS Audi,
    MAX(CASE WHEN pd.make = 'Mercedes Benz' THEN pd.days_since_last_available ELSE NULL END) AS `Mercedes Benz`,
    MAX(CASE WHEN pd.make = 'Ford' THEN pd.days_since_last_available ELSE NULL END) AS Ford
FROM PivotedData pd
GROUP BY pd.dealership_ID;

    Break through:

    1. Filter the dataset:
      For every dealership and make, find out whether the car is in stock now or the most recent sold_date for out-of-stock cars.
    2. Days since last in stock:
      Calculate date difference between today and the most recent sold_date for not currently in stock cars.
    3. Pivot the data:
      Rows to columns on each car make, respective values 0, the number of days or NULL.
    dealership_ID BMW Audi Mercedes Benz Ford
    612 0 61 0 NULL
    Login or Signup to reply.
  2. 0

    Rather than messing with dynamic pivots, you should just return the result in long form.

    You can use some fairly simple left-join and aggregation logic for this, but you need a table which lists all possible Makes, or put them in a VALUES clause.

    SELECT
  m.make,
  CASE WHEN COUNT(*) FILTER (WHERE available AND sold_date IS NULL) > 0 THEN 0
       ELSE current_date - MAX(sold_date)
       END
FROM Make m
LEFT JOIN CarAvailability ca ON ca.make = m.make
  AND ca.dealership_id = 612
GROUP BY
  m.make;

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search