This is a short guide to getting ranking or rating from Microsoft Sql Server customers.

Ranking is taken by checking how many times a customer has visited a particular country divided by the total number of travels the customer has made within the database

/*make a back up of the recommender rankings table*/
truncate table antrak.dbo.recommender_rankings_copy

insert into antrak.dbo.recommender_rankings_copy
select * from antrak.dbo.recommender_rankings


/*select total number of times a customer has travelled to a specific country*/
select
[First Name] 
,Country
, count(*) as [# times visited]
into #travelpercountry 
FROM 
[Antrak].[dbo].[FlightBookingsMain]
group by 
[First Name]
,Country
order by [First Name]

/*total number of times each customer has travelled to all destinations*/
select
[First Name] 
, count(*) as [# times visited]
into #totaltravel
FROM 
[Antrak].[dbo].[FlightBookingsMain]
group by 
[First Name]
----,Country
order by [First Name]


-- lets view by the total bookings
select * from #totaltravel order by [First Name]

-- lets view bookings per country
select * from #travelpercountry order by [First Name]


/*get rating*/
drop table antrak.dbo.recommender_rankings
select
tt.[First Name] 
,Country
,cast(round((cast(tpc.[# times visited] as decimal(3,0)) / cast(tt.[# times visited] as decimal(3,0)) * 100), 1) as float) as rating 
into antrak.dbo.recommender_rankings 
from 
#totaltravel as tt

inner join 
#travelpercountry as tpc
on tt.[First Name] = tpc.[First Name]
order by [First Name]


select * from antrak.dbo.recommender_rankings

/*lets refresh the temporary tables*/
drop table #totaltravel
drop table #travelpercountry

People Who Read The Above Post Also Read This:

Using Subqueries To Join 2 More Tables Which Do Not Have Unique Primary and Foreign Keys Get Numbers and Percentages from Tables – Microsoft Sql Server Great Quick Transact-SQL Server Tutorials – Quick Revision and General Understanding Superset Heroku Installation – Microsoft SQL Server Database Connection on Windows PC