import sqlite3

conn = sqlite3.connect('season.sqlite')
cur = conn.cursor()

season_number = input('Enter season id:')

sqlstr = '''SELECT a.home_team AS team_name, a.Points+b.Points as Points
FROM(
SELECT DISTINCT t.name AS home_team, s.name, SUM(CASE
                                         WHEN substr(m.result,1,1) > substr(m.result,-1,1) THEN '3'
                                         WHEN substr(m.result,1,1) < substr(m.result,-1,1) THEN '0'
                                         WHEN substr(m.result,1,1) = substr(m.result,-1,1) THEN '1'
                                         END) AS Points
FROM Match m JOIN Team t
ON t.id=home_team_id
JOIN Season s
ON s.id = m.season_id
WHERE season_id = season_number
GROUP BY t.name
ORDER BY Points DESC) AS a

JOIN(SELECT DISTINCT t.name AS guest_team, s.name, SUM(CASE
                                         WHEN substr(m.result,1,1) < substr(m.result,-1,1) THEN '3'
                                         WHEN substr(m.result,1,1) > substr(m.result,-1,1) THEN '0'
                                         WHEN substr(m.result,1,1) = substr(m.result,-1,1) THEN '1'
                                         END) AS Points
FROM Match m JOIN Team t
ON t.id=guest_team_id
JOIN Season s
ON s.id = m.season_id
WHERE season_id = season_number
GROUP BY t.name
ORDER BY Points DESC) AS b
ON a.home_team=b.guest_team
ORDER BY Points DESC'''

cur.execute(sqlstr)
result = cur.fetchall()
for r in result:
    print(r)