Get periods of each segment for each client

user_15547164 0
Viewed 32

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:

client_id segment_id "first_value" "last_value"
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:

client_id segment_id "first_value" "last_value"
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.

mysql sql
2 Answers

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;
user_153326500

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
user_124928900
Related