Calculate User Popularity Percentage with SQL CTEs
In this video, we explore how to calculate the popularity percentage for each user on Meta/Facebook using SQL Common Table Expressions (CTEs). Learn to:
Calculate Total Number of Friends: Find out how many friends each user has.
Determine Popularity Percentage: Compute the percentage of friends relative to the total number of users.
Output Results: Display the results in ascending order by user ID.
Script available :https://shorturl.at/HcNrp
/* Find the popularity percentage for each user on Meta/Facebook.
The popularity percentage is defined as the total number of friends the user has divided by the
total number of users on the platform, then converted into a percentage by multiplying by 100.
Output each user along with their popularity percentage. Order records in ascending order by user id.
*/
drop table facebook_friends;
create table facebook_friends
(
user1 int,
user2 int
);
insert into facebook_friends values (2,1);
insert into facebook_friends values (1,3);
insert into facebook_friends values (4,1);
insert into facebook_friends values (1,5);
insert into facebook_friends values (1,6);
insert into facebook_friends values (2,6);
insert into facebook_friends values (7,2);
insert into facebook_friends values (8,3);
insert into facebook_friends values (3,9);
select * from facebook_friends;
how many frnds does user id "2" have ? == 1 + 1 + 1 = 3
how many frnds does user id "1" have ? == 2, 3, 4, 5, 6 = 5 frnds
a) Total no of users in FB -- found from cte 3
b) No of friends each user has -- found from cte 2
c) = (b / a) * 100
select count(distinct users) from (
select user1 as users from facebook_friends
union all
select user2 as users from facebook_friends) x;
with all_users as
(select user1 as users from facebook_friends
union all
select user2 as users from facebook_friends),
no_of_frnds as
(select users, count(1) as total_frnds
from all_users
group by users),
total_no_of_users as
(select count(distinct users) tot_users from all_users)
select users--, total_frnds, tot_users
,CAST(CAST(n.total_frnds AS DECIMAL) / CAST(t.tot_users AS DECIMAL)*100 as decimal(5,2)) as pop_perc
from no_of_frnds n
cross join total_no_of_users t
order by users;
SQL for Beginners: A Complete Introduction to SQL Basics!
• SQL for Beginners: A Complete Introduction...
Master SQL Commands: DDL, Data Types & Constraints Explained!
• Master SQL Commands: DDL, Data Types & Con...
Mastering SQL Constraints: Primary Key, Unique Key, Not Null, and CHECK Explained!
• Mastering SQL Constraints: Primary Key, Un...
Understanding SQL Foreign Keys: Building Relationships Between Tables
• Understanding SQL Foreign Keys: Building R...
SQL Identity Column: Auto-Generating Unique Values for Your Tables
• SQL Identity Column: Auto-Generating Uniqu...
SQL DML & DDL Commands Explained | Insert, Update, Delete, Truncate, Drop
• SQL DML & DDL Commands Explained | Insert,...
Master Basic SQL Queries & Operators | Part 1: Fetch, Filter & Count
• Master Basic SQL Queries & Operators | Par...
SQL Functions and Queries: Aggregate, CAST, REPLACE, ROUND, GETDATE
• SQL Functions and Queries: Aggregate, CAST...
Mastering SQL INNER JOIN: Essential Queries & Examples
• Mastering SQL INNER JOIN: Essential Querie...
SQL Group By Explained with Examples | Master Aggregate Queries
• SQL Group By Explained with Examples | Mas...
Mastering SQL: Group By, Having, CASE, Order By, Join, Top, and Limit
• Mastering SQL: Group By, Having, CASE, Ord...
Introduction to SQL Normalization: 1NF (First Normal Form) - Part 1
• Introduction to SQL Normalization: 1NF (Fi...
Understanding SQL Normalization: 2NF (Second Normal Form) - Part 2
• Understanding SQL Normalization: 2NF (Seco...
Mastering SQL Normalization: 3NF (Third Normal Form) Explained - Part 3
• Mastering SQL Normalization: 3NF (Third No...
Master SQL Subqueries: Scalar, Multi-Row, and Correlated Subqueries Explained
• Master SQL Subqueries: Scalar, Multi-Row, ...
SQL Tutorial: Remove Duplicate Data Efficiently | Common Interview Question
• SQL Tutorial: Remove Duplicate Data Effici...
SQL Joins Explained: Master INNER, LEFT, RIGHT, FULL, CROSS & SELF Joins with Examples
• SQL Joins Explained: Master INNER, LEFT, R...
Master SQL Joins: Self Joins, Outer Joins, and More!
• Master SQL Joins: Self Joins, Outer Joins,...
SQL Queries Using CTE: Profitability & Monthly Sales Differences
• SQL Queries Using CTE: Profitability & Mon...
Calculate User Popularity Percentage with SQL CTEs
• Calculate User Popularity Percentage with ...
SQL Window Functions Explained: ROW_NUMBER, RANK & DENSE_RANK
• SQL Window Functions Explained: ROW_NUMBER...
SQL Window Functions Explained: LEAD & LAG with Real Examples!
• SQL Window Functions Explained: LEAD & LAG...
Master SQL Window Functions: FIRST_VALUE, LAST_VALUE, and More!
• Master SQL Window Functions: FIRST_VALUE, ...
Easily Delete Duplicate Rows in SQL Server with ROW_NUMBER()
• Easily Delete Duplicate Rows in SQL Server...
Understanding Recursive CTEs in SQL: A Simple Guide
• Understanding Recursive CTEs in SQL: A Sim...
Master SQL PIVOT: Transform Rows into Columns
• Master SQL PIVOT: Transform Rows into Columns
We'll guide you through using CTEs to simplify complex SQL queries and make your data analysis more effective. Don't forget to like, subscribe, and hit the bell icon for more SQL insights!