import snowflake.connector
import pandas as pd

USER = '<your username>'
PASSWORD = 'your password>'
ACCOUNT = 'orchard'

# Connecting to Snowflake
cnx = snowflake.connector.connect(
  user=USER,
  password=PASSWORD,
  account=ACCOUNT,
)


cur = cnx.cursor()

labelid = input("Enter label id: ")
labelid = [str(labelid),str(labelid)]

# query historical avg, standard dev for top 20% of label's tracks
historical_query = (
"		select trackid, 	"								
"		trackname, "
"		artistname, "
"		percent_rank,"
"		avg(sums) as AVG_SUMS, "
"		STDDEV(sums) AS STD_SUMS "
"		from "
"		(select dt.trackname as trackname, "
"		 da.artistname as artistname, "
"		DATE_TRUNC('day',fa.download_activity_date) as day, "
"		dt.trackid as trackid,"
"		percent_rank,"
"		sum(fa.units) as sums"
"		from ("
"		  select trackid"
"		  , sum(units)"
"		  ,  percent_rank() over (order by sum(units)) as percent_rank "
"		  from FACTS.PROD.FACT_ANALYTICS fa"
"		  where transactiontypeid in (1,10,48)"
"		  and storeid in (286)"
"		  and fa.download_activity_date = DATEADD(day, -3, CURRENT_DATE)"
"		  and labelid = %s"
"		  group by 1"
"		  order by 2 desc"
"		  limit 5000"
"		) topten"
"		inner join FACTS.PROD.FACT_ANALYTICS fa on topten.trackid = fa.trackid "
"		join FACTS.PROD.DIM_TRACK dt on fa.trackid = dt.trackid"
"		join FACTS.PROD.DIM_ARTIST da on fa.artistid = da.artistid"
"		where fa.transactiontypeid in (1,10,48)"
"		and fa.download_activity_date between DATEADD(day, -93, CURRENT_DATE) and DATEADD(day, -3, CURRENT_DATE)"
"		and fa.storeid in (286)"
"		and fa.labelid = %s"
"		and topten.percent_rank>.8"
"		group by dt.trackname, da.artistname,3,4,5"
"		order by 3 desc) allrows"
"		group by 1,2,3,4"
"		order by AVG_SUMS desc"
)


# query today's streams for top 20% of label's tracks
today_query = (
"		select dt.trackname, dt.trackid, da.artistname, percent_rank,"
"		sum(fa.units) as today"
"		from ("
"		 select trackid"
"		  , sum(units)"
"		  ,  percent_rank() over (order by sum(units)) as percent_rank "
"		  from FACTS.PROD.FACT_ANALYTICS fa"
"		  where transactiontypeid in (1,10,48)"
"		  and storeid in (286)"
"		  and fa.download_activity_date = DATEADD(day, -3, CURRENT_DATE)"
"		  and labelid = %s"
"		  group by 1"
"		  order by 2 desc"
"		  limit 5000"
"		) topten"
"		inner join FACTS.PROD.FACT_ANALYTICS fa on topten.trackid = fa.trackid "
"		join FACTS.PROD.DIM_TRACK dt on fa.trackid = dt.trackid"
"		join FACTS.PROD.DIM_ARTIST da on fa.artistid = da.artistid"
"		where fa.transactiontypeid in (1,10,48)"
"		and fa.download_activity_date = DATEADD(day, -3, CURRENT_DATE)"
"		and fa.storeid in (286)"
"		and fa.labelid = %s "
"		and topten.percent_rank>.8"
"		group by dt.trackname, dt.trackid, da.artistname, percent_rank"
"		order by 4 desc"
)


print "Querying historical data..."
hist_df = pd.read_sql(historical_query, cnx, params=labelid)

print "Querying today's data..."
today_df = pd.read_sql(today_query, cnx, params=labelid)


import numpy as np

#merge two dataframes
all_tracks = pd.merge(hist_df,today_df, on=['TRACKID','TRACKNAME','ARTISTNAME',"PERCENT_RANK"])

#calculate z-score
all_tracks['zscore'] = (all_tracks['TODAY']-all_tracks['AVG_SUMS'])/all_tracks['STD_SUMS']

#calculate percent above average streams
all_tracks['pct_above_avg'] = (all_tracks['TODAY'] - all_tracks['AVG_SUMS'])/all_tracks['AVG_SUMS']*100

all_tracks = all_tracks.sort_values('zscore',ascending=False)

print all_tracks[:10]


trending_ids = []

for i, id in all_tracks.iterrows():
	if id['zscore']>4.5:
		trending_ids.append(id['TRACKID'])

if not trending_ids:
	print "You have no trending tracks today."
else:	
	print trending_ids