Using coalesce in joins

Question

I'm writing a query that lists all the challenges (+ score) a user has taken today, or in case he hasn't taken any challenge on this day that outputs the last challenge he has ever taken.

I've written the following query, that seems to give me what I want, but it seems like a very convoluted way of achieving this. I was wondering if there isn't a better way of achieving the same result.

SELECT COALESCE (c.id, coal) challenge_id, max(e.accuracy_score) score 
FROM (select id, creation_date from challenge WHERE learner_id = $1 AND creation_date > CURRENT_DATE) c 
FULL OUTER JOIN
        COALESCE (
            (SELECT id FROM challenge WHERE learner_id = $1 AND creation_date > CURRENT_DATE LIMIT 1),
            (SELECT id FROM challenge WHERE learner_id = $1 ORDER BY creation_date DESC LIMIT 1)

    ) AS coal
 ON coal = c.id 
 LEFT JOIN experience e ON COALESCE (c.id, coal)=e.challenge_id 
 GROUP BY COALESCE (c.id, coal) ORDER BY COALESCE (c.id, coal) ASC

Answer 1

In Postgres, I think the easiest method uses window functions. If you wanted all the challenges on the most recent date:

select c.*
from (select c.*,
             dense_rank() over (partition by learner
                                order by date_trunc('day', creation_date) desc
                               ) as seqnum
      from challenge c
     ) c
where seqnum = 1;

I have no idea what the experience table is.

If you really do want all challenges from the current date and only the most recent (as opposed to all challenges on the most recent date), then use row_number() and some additional date logic:

select c.*
from (select c.*,
             row_number() over (partition by learner
                                order by creation_date desc
                               ) as seqnum
      from challenge c
     ) c
where date_trunc('day', creation_date) = CURRENT_DATE or
      seqnum = 1;