Calculate the difference of dates in one column

DB-Fiddle

CREATE TABLE logistics 
(
    id SERIAL PRIMARY KEY,
    campaign VARCHAR(255),
    order_date DATE
);

INSERT INTO logistics (campaign, order_date)
VALUES ('C001', '2021-01-01'),
       ('C001', '2021-01-05'),

       ('C002', '2021-05-18'),
       ('C002', '2021-06-10'),

       ('C003', '2021-11-12'),
       ('C003', '2021-11-13');

Expected result:

 campaign  |  date_difference  |
-----------+-------------------+
   C001    |          4        |
   C002    |         23        |
   C003    |          1        |

I want to calculate the date_difference between the MAX and the MIN order_date per campaign.

So far I was able to come up with this query:

SELECT
    l.campaign AS campaign, 
    l.order_date AS order_date,
    (DATE_PART('day', l.order_date) - DATE_PART('day', l.order_date)) AS date_difference
FROM 
    logistics l
ORDER BY 
    1, 2;

This solution would work if the dates would be in two separate columns.

How do I have to modify the query to also make it work if they are in one column?

Answer

date_part(day, ...) returns the day of the month, not the number of days for that date. If you want the difference in days, just subtract the two dates. As you want the min() and max() dates, you will have to group by the campaign to do that.

SELECT l.campaign,
       max(l.order_date) - min(l.order_date) as date_difference
FROM logistics l
GROUP BY l.campaign

Online example