Another advantage of NAA/VLQ handling via review is that the poster can undelete by themself after fixing issues, which they can't do after a moderator deletion

Yes that's a good point

A chairde - Morning guys!

This is why the odd "Hi, and welcome to the forum" in comments is not misplaced... I'll put a post onto meta- sometime this week, and there's also this to consider...

@Vérace Not at all. You can't see what prompted me to leave that comment. Putting "hi and welcome to the forum" would not have fixed it. Anyway, good luck on meta. You're bound to hit gold eventually!

Tempted to down vote at the moment.

Votes do work both ways.

Can't blame PG for doing standard integer math

Anyway, the answer has been edited now. Maybe not perfectly, but hey.

A comment was flagged as no longer needed so I set fire to the lot.

@ypercubeᵀᴹ - I noticed that you took an interest in this answer - so did I - I have a couple of questions pursuant to my answer - 1) I'm a bit confused as to why here (TO_DATE(cyr::TEXT || cwk::TEXT, 'YYYYWW') + INTERVAL '3 DAY')::DATE it is necessary to add 3 days to get the correct start of the week? I got that code from here.

Also, I tried to generate the year as a virtual column by doing EXTRACT(YEAR:: TEXT... but no matter what I tried, it gave me something about the column being not deterministic - but, AFAICS, it's as deterministic as EXTRACT(week FROM car_time)... any ideas?

> I don't see why you require a CROSS JOIN in this case! They are performance killers!

Cross-joining to two table expressions guaranteed to have exactly one row is fine.

OK - but all things being equal, CROSS JOINs are not the best choice, surely? I mean the LAG function removes any need for joining at all - consider this performance analysis - plus it gives the right answers!

and just before me clicking on the "move discussion to chat", comments were deleted ;)

@Vérace Yes, accidental cross joins should be avoided. Window functions are sometimes a big win, sometimes not. It depends.

It could be more efficient, yes, if you can remove a cte/subquery,. Not because you remove the cross join.

@ypercubeᵀᴹ I can restore them if you think the discussion is worth saving in a chat room, or if you want to pursue it. I just handled the flag I saw in my normal way.

It could also result in less efficient query

Nah, leave it.

A subquery supported by correct indexing can be optimal. It all depends.

@ypercubeᵀᴹ okidoki

I am only on my phone at the moment, that's why I did not respond to the last comments.

yep

@Vérace I ll try to check later. Not sure what exactly you are doing and why you need conversion to text

@ypercubeᵀᴹ I'm looking for a nice way of providing a week_starting field... I know it can be derived down the line - but since it's nice and simple - I'd like to do it in the table definition - even just as an exercise - re comments: TDLOTDB has struck again... and also, I'd like to understand why one field derived from a function on DATE is considered deterministic and other are not?

@PaulWhite - I did put in performance analyses - I think that a WINDOW function trumps a JOIN anyday - at least as far as I"ve seen with PostgreSQL so far...

@Vérace In the cases you have tried. Beware confirmation bias.

@PaulWhite - you changed (my correct usage) of "it's the same as above" to "it is the same as above" - also correct, but why (the $%^&*)? Has TDLOTDB lost the run of himself? Has one of the evil clones taken over? Was there an ulterior motive?

@Vérace Do you ever read the revision history?

Did you read the words "Single quotes mess with syntax highlighting"

I mean I literally left you an explanation of the one-character edit

Oooops... didn't notice the reason for edit... and yes, I saw the green text and wondered about it... thanks for taking the time to correct that - and esp. for providing a reason - it makes it all the easier to remember for next time... <busily scoffing humble pie...>

sounds tasty

I wish they'd fix it, but they won't

Well the conversation started up again so I moved everything to a chat room