40 lines
1.3 KiB
SQL
40 lines
1.3 KiB
SQL
SELECT
|
|
cluster,
|
|
id,
|
|
worker,
|
|
evidence_type,
|
|
evidence,
|
|
category,
|
|
polarity,
|
|
created_at,
|
|
created_by
|
|
FROM (
|
|
SELECT
|
|
cluster,
|
|
id,
|
|
worker,
|
|
evidence_type,
|
|
evidence,
|
|
category,
|
|
polarity,
|
|
created_at,
|
|
created_by,
|
|
ROW_NUMBER() OVER (PARTITION BY category ORDER BY created_at DESC) AS rn
|
|
FROM (
|
|
(
|
|
SELECT "user" as cluster, id, product_id, null as worker, evidence_type, evidence, unnest(claim) as category, true as polarity, created_at, created_by FROM user_claims
|
|
UNION ALL
|
|
SELECT "automated" as cluster, id, product_id, worker, null as evidence_type, evidence, unnest(claim) as category, true as polarity, created_at, null as created_by FROM automated_claims
|
|
)
|
|
UNION ALL
|
|
(
|
|
SELECT "user" as cluster, id, product_id, null as worker, evidence_type, evidence, unnest(counter_claim) as category, false as polarity, created_at, created_by FROM user_claims
|
|
UNION ALL
|
|
SELECT "automated" as cluster, id, product_id, worker, null as evidence_type, evidence, unnest(counter_claim) as category, false as polarity, created_at, null as created_by FROM automated_claims
|
|
)
|
|
)
|
|
WHERE product_id = ???
|
|
)
|
|
WHERE rn = 1
|
|
ORDER BY created_at;
|