Blog

Introduction

• In this article we will solve Teams Power Users SQL question which is asked by Microsoft as per DataLemur website.

If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.

Question

• We have been given messages table which contains message_id, sender_id along with other columns.
• We have to find power users who sent most message in month of august in year 2022.

• Our output contains total message count for each power user, we have to output top 2 users.

Solution

• Since we need to output total number of messages sent by each user, that makes clear that we need to use group by sender and count message_id on that group.
• This would return below output. but it contains message for all the years and month, as per our question we need to only need to count message send in august 2022, hence that would be added to where clause in our sql.

select sender_id, count(message_id) as message_count from messages
group by sender_id

• our send_date is in datetime format, so basically we can extract month and year from it easily using extract from . Once we have month and year we can filter our input table for august and 2022 year.

select sender_id, count(message_id) as message_count from messages
where extract(month from sent_date) = '8' and extract(year from sent_date) = '2022'
group by sender_id
order by message_count desc
limit 2

• We are ordering by message_count and limiting the result to 2 as asked in question.

Submission

• Our solution is accepted by platform.

Conclusion

• In this article , we solve sql question asked by Microsoft. We learn how to use group by , aggregation operation to our advantage when need to provide top level KPI’s.
• We also visited the syntax to grab the month and year from datetime column.

Bonus Tip

• If you want to upskill your SQL interview game, you can definitely check out this bestseller course