SQL Interview Questions Part 53 | Google SQL Question | SQL Data Engineer Interview | Activity Rank

SQL Interview Questions Part 53 | Google SQL Question Part-10 | SQL Data Engineer Interview 🔥🔥

Write a SQL query to find the email activity rank for each user.
Email activity rank is defined by the total number of emails sent. The User with the highest number of emails sent will have a rank of 1 and so on. Output the user, total emails and their activity rank. Order records by the total emails in descending order . Sort users with the same number of emails in alphabetical order.
While displaying rankings , return unique ranks even if multiple users have the same number of emails.

-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
🔴 Instagram :

🔴 Twitter:
-------------------------------------------------------------------------
🔴 Table and Insert SQL Script :
-------------------------------------------------------------------------
Create Table google_gmail_emails (
Id int,
From_User varchar(40),
To_User varchar(40),
Days int
)
**I have provided the INSERT SQL scripts in git repository in below link as there were 315 records.

#GoogleDataEngineer #GoogleInterview #GoogleSQLInterview #FAANG #SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #ITJunction4all

[kuls]

Thanks for your all previous videos which helped me a lot. I was able to resolve this query in no time.

[prashant]

with dups as (select a, b
from Reverse_duplicates
union all
select b, a
from reverse_duplicates)
select distinct
case when a<b then a else b end as a,
case when a>b then a else b end as b
from dups

[vijay.s-llyq]

with cte as
(select distinct From_User, count(from_user) over (partition by from_user order by from_user ) as final
from google_gmail_emails)

select *, row_number() over(order by final desc, from_user) from cte

[dhyeypatel]

with cte as(
select from_user, count(*) as total_mailsent
from google_gmail_emails
group by from_user
)

select *, row_number() over(order by total_mailsent desc, from_user) as activity_rank
from cte

[reachrishav]

SELECT
from_user

, count(1) as email_cnt
, ROW_NUMBER() OVER(ORDER BY COUNT(1) DESC, from_user) rn
FROM google_gmail_emails
GROUP by from_user
ORDER BY 2 desc