Find the penultimate activity for each user

I have a sample data like below:

Username	Activity	Start Time	End Time
Ace	Dancing	13:00	14:00
Ace	Singing	15:00	16:30
Ace	Yoga	19:00	20:00
Alice	Piano	10:00	11:00
Alice	Hiking	14:00	15:00
Alice	Reading	16:00	16:30
Alice	Swimming	19:00	20:00
Alice	Writing	21:00	21:30
Lion	Fishing	13:00	17:00

I want to find the penultimate activity for each user, if only records one activity, then output that activity, that is, I would like to get the following output:

Username	Penultimate_Act
Ace	Singing
Alice	Swimming
Lion	Fishing

I tried but it cannot get Lion:

SELECT username, activity AS penultimate_act
FROM (
    SELECT
        username,
        activity,
        DENSE_RANK() OVER (PARTITION BY username ORDER BY starttime DESC) AS seq
    FROM activities
) t
WHERE seq = 2;

Then I tried again:

SELECT username, activity AS penultimate_act
FROM (
    SELECT
        username,
        activity,
        DENSE_RANK() OVER (PARTITION BY username ORDER BY starttime DESC) AS seq
    FROM activities
) t
WHERE seq = 2 or seq = 1;

But it still doesn't quite meet my needs, could anyone provide some help?(If you haven’t heard of my database, you can use PostgreSQL to demo instead. Thank You!)

Solution

I would keep the query you have and then add a second query to fetch those usernames with their activity which appear once only. You can combine the two queries with UNION ALL.

Use a GROUP BY clause with HAVING to select only those usernames occuring only once:

-- your existing query...
UNION ALL
SELECT username, MAX(activity)
FROM activities
GROUP BY username
HAVING COUNT(*) = 1;

Note: It might be faster to use ROW_NUMBER rather than DENSE_RANK in your query.

Both will produce the expected result for your sample data:

Username	Penultimate_Act
Ace	Singing
Alice	Swimming
Lion	Fishing

See the demo.