# HG changeset patch
# User Laman
# Date 2018-03-27 13:20:43
# Node ID 6127b170f21c43302562998be9db9cd26647ae2b
# Parent  d72a896090958e5cf24e53a89ae19b2687ed4fa0

populationStrength, indexy na tabulkách

diff --git a/egd.py b/egd.py
--- a/egd.py
+++ b/egd.py
@@ -67,6 +67,9 @@ def setupDB():
 				from players_performance as pp join tournaments as ts on pp.tournament_code=ts.code
 	""")
 
+	cursor.execute("""create index pp_country_ind on players_performance (country_code)""")
+	cursor.execute("""create index t_datum_ind on tournaments (datum)""")
+
 	connection.commit()
 
 
diff --git a/queries.py b/queries.py
--- a/queries.py
+++ b/queries.py
@@ -59,14 +59,32 @@ def timeRank(year,country):
 		print("\t".join([str(x) for x in rec]))
 
 
-def populationStrength():
-	pass
+def populationStrength(year,country):
+	date1=yearStart(year)
+	date2=yearStart(year+1)
+	query="""
+		select round((rating_after-50)/100)*100 as rating, count(ppt.pin)
+			from players_performance_tournaments as ppt
+				join (select pin,max(datum) as max_datum from players_performance_tournaments where datum>=date(?) and datum<date(?) and p_country_code=? group by pin) as subq on ppt.pin=subq.pin and ppt.datum=subq.max_datum
+			group by rating
+			order by rating desc
+	"""
+
+	res=cursor.execute(query,(date1,date2,country))
+	print("rating\tpočet hráčů s takovým a vyšším ratingem")
+	for rec in res:
+		print("\t".join([str(x) for x in rec]))
 
 
 def populationAge():
 	pass
 
 
+def tournamentAttendance():
+	pass
+
+
 # playerYear("Kotowski Jaroslav",2017)
 # yearByCountry(2017)
-timeRank(2017,"CZ")
+# timeRank(2017,"CZ")
+# populationStrength(2017,"CZ")