eu quero selecionar a primeira e a última pontuação desses dados
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;
minha pergunta: existe uma maneira de fazer uma subconsulta para juntar o first_score e last_score em uma única consulta?
