day1.sql

create database challenge

----------------------------------Day1----------------------------------
------------------------------------------------------------------------

--Tables Creations & Alter
create table Users
(
	User_ID int primary key
)

create table Famous
(
	User_ID int foreign key references Users (User_ID),
	Follower_ID int foreign key references Users(User_ID)
)

alter table Famous 
alter column User_ID int Not Null
GO
alter table Famous 
alter column Follower_ID int Not Null
GO
alter table Famous
add constraint Famous_PK primary key (User_ID, Follower_ID)
GO
alter table Famous
add constraint No_SelfFollow CHECK (User_ID <> Follower_ID)

--Insert Data
INSERT INTO Users VALUES (1);
INSERT INTO Users VALUES (2);
INSERT INTO Users VALUES (3);
INSERT INTO Users VALUES (4);
INSERT INTO Users VALUES (5);
INSERT INTO Users VALUES (6);
GO
INSERT INTO Famous VALUES (1,2);
INSERT INTO Famous VALUES (1,3);
INSERT INTO Famous VALUES (1,4);
INSERT INTO Famous VALUES (2,1);
INSERT INTO Famous VALUES (2,3);
INSERT INTO Famous VALUES (3,1);
INSERT INTO Famous VALUES (4,1);
INSERT INTO Famous VALUES (4,2);
INSERT INTO Famous VALUES (4,3);
INSERT INTO Famous VALUES (4,5);
INSERT INTO Famous VALUES (5,1);
INSERT INTO Famous VALUES (6,2);
INSERT INTO Famous VALUES (6,3);
GO

--Retrieve The Data
select *
from day1.Users

select * 
from day1.Famous

--
create view VW_TotalFollowers
as
select F.User_ID, count(*) as 'Total Followers'
from Famous F
group by F.User_ID

select * from day1.VW_TotalFollowers

create view VW_TotalUsers
as
select count(*) as 'Total Users' 
from Users U

select * from day1.VW_TotalUsers

--final step
select 
	F.User_ID,
	format(round(1.0* F.[Total Followers]/U.[Total Users],2),'0.##') as Famous_percentage
from day1.VW_TotalFollowers F cross join day1.VW_TotalUsers U

--final step (another way)
select
	F.User_ID, format(round(1.0 * F.[Total Followers] / (select count(*) from day1.Users), 2),'0.##') as Famous_percentage
from day1.VW_TotalFollowers F

--create schema
create schema day1

alter schema day1 transfer dbo.Users
alter schema day1 transfer dbo.Famous
alter schema day1 transfer dbo.VW_TotalFollowers
alter schema day1 transfer dbo.VW_TotalUsers