Cross joining 3 columns and need to eliminate null rows if null across certain conditions

Question

Ok, this is kind of hard to explain, but I'll do my best here. I'm crossing joining on 3 columns and need to eliminate certain rows so that the results aren't excessively large. I think this is best explained with a fiddle.

I have two tables:

ticks(dait DATE, tick INT);

votes(dayt DATE, tick INT, user INT, vote INT);

And I need to cross join them on dait, tick, user. Here's where the fiddle is helpful.

https://www.db-fiddle.com/f/9phZ7EpRUS4FNRBZRdSYZa/29

And here's the query I've tried that's gotten me the closest:

SELECT x.user, ticks.tick, dait, vote, ROW_NUMBER() OVER (ORDER BY x.user, ticks.tick, 
dait) AS ilocat
FROM ticks
CROSS JOIN (SELECT DISTINCT dayt, user, tick FROM votes) x
LEFT JOIN votes
ON votes.dayt = ticks.dait AND votes.tick = ticks.tick AND x.user = votes.user
GROUP BY dait, ticks.tick, x.user
ORDER BY x.user, ticks.tick, dait;

You can see user 12 has only voted once on tick 3 on 2021-10-27. The output of the fiddle is very close to what I want BUT I need to eliminate rows (ilocat) 19-24 that show all null votes for ticks 1 and 2 from user 12 across the date range.

In other words, if a user has never voted on a tick, I do not want to return those rows. BUT, if a user has voted at least once on a tick, then I want to return all rows corresponding to all dates (and show null votes on the days the user didn't vote).

The reason why I need to see all the dates the user didn't vote is because I am performing later queries to find the streaks of the user over each tick they've voted on. As you can imagine, the query I've tried will yield very large results if I am unable to eliminate those unnecessary rows.

Answer 1

if a user has never voted on a tick, I do not want to return those rows. BUT, if a user has voted at least once on a tick, then I want to return all rows corresponding to all dates

You should change the CROSS join to an INNER join:

SELECT x.user, t.tick, t.dait, v.vote, 
       ROW_NUMBER() OVER (ORDER BY x.user, t.tick, dait) AS ilocat
FROM ticks t
INNER JOIN (SELECT DISTINCT dayt, user, tick FROM votes) x
ON t.tick = x.tick
LEFT JOIN votes v
ON v.dayt = t.dait AND v.tick = t.tick AND x.user = v.user
GROUP BY dait, t.tick, x.user
ORDER BY x.user, t.tick, t.dait;

See the demo.