SQL Hacks - Day 3

Alright, so I have started to get a good hang of Leetcode problems and I am loving it!

But since I was able to solve questions that were either easy and medium, I made it a point to check what other people were posting. My queries were taking a lot of time so I wanted to ensure that they are optimized.

That's when I learnt about CTE (Common Table Expression). I am not here to teach you this concept, but provide you with adequate resources to get started.

CTE is like a subquery also called "with queries" because they use with

Link to learn from : https://www.youtube.com/watch?v=K1WeoKxLZ5o&ab_channel=AlexTheAnalyst

Definitely a good learning!

Another thing i learnt was - that I wanted to use "where" condition in group by but as you know we can only using "having". So, I instead used "and" condition while joining, see example as below:

select

u.user_id as buyer_id,

u.join_date,

if(count(o.order_id) is null, 0,count(o.order_id)) as orders_in_2019

from Users u

left join

Orders o

on u.user_id=o.buyer_id

and year(o.order_date)=2019

group by u.user_id

That's all for today folks! I solved 3 problems today.

Good night!