1

i want to select first and last score from such data

WITH t AS (
SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score
UNION ALL
SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as score
UNION ALL
SELECT 2 user_id, '2019-05-15'::date AS created_at, 7 as score
UNION ALL
SELECT 3 user_id, '2019-05-13'::date AS created_at, 6 as score
),
first_score AS (
    SELECT DISTINCT ON (user_id)
        created_at, score, user_id
    FROM t
    ORDER BY user_id, created_at ASC
),
last_score AS (
    SELECT DISTINCT ON (user_id)
        created_at, score, user_id
    FROM t
    ORDER BY user_id, created_at DESC
)

select
    md5(u.user_id::varchar) user_id,
    u.created_at signup_date,
    fhs.created_at first_score_created_at,
    fhs.score first_score,
    lhs.created_at last_score_created_at,
    lhs.score last_score
from t as u 
    INNER JOIN first_score fhs ON fhs.user_id = u.user_id
    INNER JOIN last_score lhs ON lhs.user_id = u.user_id;

my question: is exists a way to make one subquery to join the first_score and last_score in single query?

2
WITH t AS (
SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score
UNION ALL
SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as score
UNION ALL
SELECT 2 user_id, '2019-05-15'::date AS created_at, 7 as score
UNION ALL
SELECT 3 user_id, '2019-05-13'::date AS created_at, 6 as score
)
SELECT
    md5(user_id::varchar) user_id,
    created_at signup_date,
    MIN(created_at) OVER (PARTITION BY user_id) first_score_created_at,
    FIRST_VALUE(score) OVER (PARTITION BY user_id ORDER BY created_at ASC) first_score,
    MAX(created_at) OVER (PARTITION BY user_id) last_score_created_at,
    FIRST_VALUE(score) OVER (PARTITION BY user_id ORDER BY created_at DESC) last_score
FROM t
  • PS. You think that you can use LAST_VALUE(score) .. last_score for to use the same window as for first_score? Sorry, but it can give wrong result (seems to be a bug)... – Akina May 15 at 8:26
  • It's no bug, it's to do with the default window frame. Basically, when you specify ORDER BY, the frame ends at the current row (it's slightly more complicated than that but will do for this scenario), unless you explicitly specify it to be something else. If you set the frame as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to cover the entire partition, you can use LAST_VALUE just fine. See here for more details. – Andriy M May 15 at 10:50
  • @AndriyM Thanks. In my queries I always set rows/range, so I have no seen this problem... and here I tried to save bytes... – Akina May 15 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.