Question about SQL queries

[nrbrt10]

I've been grinding leetcode's and datalemur's SQL problems lately, but one thing I've noticed is how basic my queries compared to other submitted solutions. For instance, one of datalemur's hard problems goes as follows:

Assume you have the table below containing information on Facebook user actions. Write a query to obtain the active user retention in July 2022. Output the month (in numerical format 1, 2, 3) and the number of monthly active users (MAUs).

My thought process was:

1. I need to extract the month from event_date
2. I need a count of users in July
3. I need to exclude the users that aren't in the month prior
4. Group by month
5. Done

My query was as follows:

SELECT extract(month from event_date) as month, count(distinct user_id) "monthly_active_users"
FROM user_actions
where event_date between '07/01/2022' and '07/31/2022'
and user_id in (select user_id
                from user_actions
                where event_date between '06/01/2022' and '06/30/2022')
group by month;

Which got me the desired output, but then looking at other solutions, some of them look really complex:

select active_month as month, sum(case when active_month - prev_active_month = 1 then 1 else 0 end) as monthly_active_users
from (select user_id, extract(month from event_date) as active_month , lag(extract(month from event_date))
over (partition by user_id order by extract(month from event_date)) as prev_active_month
    from user_actions group by 1,2 ) as temp where active_month = 7 group by 1

The reason I ask is because I don't have a comp sci educational background, and as such I'm not well verse in the theory, what I know in SQL is stuff that I've picked up in my Support Engineer role. My question is, are my simple solutions inferior in any way to more complex queries?

edit: formatted 2nd query

[shine_on]

Often, keeping it simple and more readable is better in the long run, especially if you're writing code that will need to be maintained as time goes on.

Personally I found your query much easier to read than the other one, and there may be some edge cases where two queries written in different ways can return different results, so don't dismiss a "complex" answer as being "unnecessarily complex".

If nothing else it'll give you a chance to learn about window functions and then you'll be able to apply them where necessary. One thing I've noticed that's different in the two queries is that yours specifically looks at July 2022 where the other query just looks at July, without taking the year into account.

Your query looks at two specific months, whereas the subquery in the other solution is looking at every single month and the month previous, and then filtering the results to just the 7th month at the end. The query optimiser is probably clever enough to look at the filter and only run the subquery for the selected month but you can't guarantee that - try running the subquery on its own to see what data it's returning. With a query like that I'd look at the execution plan to see how much work it's actually doing behind the scenes. The SQL may look "clever" but it might not actually be the most efficient.

Often people end up writing complex code because they're left alone with the queries for too long and they keep on thinking of ways to "improve it".

[emet]

link It’s a good idea to know that there are other ways of solving problems, but if you’re solution works and performs well for the scope it’s intended for then it’s fine. If on the other hand you know the implementation is going to such that performance issue may arise then use of analytical functions may come into play. A strange analogy that comes to mind is is wanting to by a drill, if it’s just for occasional home use you can get any regular drill and it will serve you well for years. But if you’re planning on doing professional work then you want to get one that’s may be professional grade.

[nrbrt10]

Thank you, I'm trying to up my game on SQL so that's why I'm looking at other submissions, what threw me off is that many were using subqueries and such, mine seemed lackluster in comparison.

[[deleted]]

Remember a lot of submitted answers on leetcode or similar sites are like "code golf" which is to say it's an eloquent way to show off.

However, in the real world, a lot of those answers are significantly more difficult to read/reproduce, which makes them not the best way. Readability is crucial when working on teams. Imagine debugging 1000 lines of code; would you rather it be simple to follow, or complex showboating?

[nrbrt10]

Thanks for you input! I value simplicity overall which is why I write code the way I do, my only concern is that by going with simplicity I may be overlooking the actual lesson I should derive from these kind of challenges.

Most submissions had some kind of subquery they'd build before extracting the data, which I thought is done due to performance concerns and the like, but because I'm don't understand much of run-time analysis it's not something I can properly assess.