Get periods of each segment for each client-CodePudding

There are monthly periods of customer segments, I need to get the periods of each segment for each client.

Dataset:

CREATE TABLE segm (
    dt DATE,
    client_id VARCHAR(6),
    segment_id INT
)

INSERT INTO segm VALUES
    ('2018-01-31' ,'A11111', 2),
    ('2018-02-28' ,'A11111', 2),
    ('2018-03-31' ,'A11111', 1),
    ('2018-04-30' ,'A11111', 1),
    ('2017-11-30' ,'B22222', 1),
    ('2017-10-31' ,'B22222', 1),
    ('2017-09-30' ,'B22222', 3),
    ('2017-09-30' ,'C33333', 1),
    ('2017-10-31' ,'C33333', 1)

I do a SQL query:

SELECT 
    client_id
    , segment_id
    , FIRST_VALUE(dt) OVER(PARTITION BY dt ORDER BY dt) AS "first_value"
    , LAST_VALUE(dt) OVER(PARTITION BY dt ORDER BY dt) AS "last_value"
FROM segm
ORDER BY client_id, segment_id, "first_value", "last_value";

I get the result:

A11111  1   2018-03-31  2018-03-31
A11111  1   2018-04-30  2018-04-30
A11111  2   2018-01-31  2018-01-31
A11111  2   2018-02-28  2018-02-28
B22222  1   2017-10-31  2017-10-31
B22222  1   2017-11-30  2017-11-30
B22222  3   2017-09-30  2017-09-30
C33333  1   2017-09-30  2017-09-30
C33333  1   2017-10-31  2017-10-31

And I need:

A11111  1   2018-03-31  2018-04-30
A11111  2   2018-01-31  2018-02-28
B22222  1   2017-10-31  2017-11-30
B22222  3   2017-09-30  2017-09-30
C33333  1   2017-09-30  2017-10-31

How can I fix the request to get the desired result?

Thanks.

CodePudding user response：

I don't think you need analytic functions, simple aggregation will give you what you need:

select Client_Id, Segment_Id, Min(dt) firstValue, Max(dt) LastValue
from segm
group by client_id, segment_id
order by Client_Id, Segment_Id;

CodePudding user response：

You can simplify your solution by using a MAX and MIN aggregation functions with a GROUP BY clause:

SELECT 
    client_id,
    segment_id,
    MIN(dt) AS first_value,
    MAX(dt) AS last_value
FROM 
    segm
GROUP BY 
    client_id,
    segment_id
ORDER BY 
    client_id, 
    segment_id, 
    first_value, 
    last_value