league match id	country_id	name 1	1	Belgium Jupiler League 1729	1729	England Premier League 4769	4769	France Ligue 1 7809	7809	Germany 1. Bundesliga 10257	10257	Italy Serie A 13274	13274	Netherlands Eredivisie 15722	15722	Poland Ekstraklasa 17642	17642	Portugal Liga ZON Sagres 19694	19694	Scotland Premier League 21518	21518	Spain LIGA BBVA 24558	24558	Switzerland Super League Showing 11 out of 11 rows match id	country_id	season	stage	date	hometeam_id	awayteam_id	home_goal	away_goal 757	1	2011/2012	1	2011-07-29	1773	8635	2	1 758	1	2011/2012	1	2011-07-30	9998	9985	1	1 759	1	2011/2012	1	2011-07-30	9987	9993	3	1 760	1	2011/2012	1	2011-07-30	9991	9984	0	1 761	1	2011/2012	1	2011-07-30	9994	10000	0	0 762	1	2011/2012	1	2011-07-30	8571	9989	1	1 763	1	2011/2012	1	2011-07-30	8203	9997	2	1 764	1	2011/2012	1	2011-07-31	8342	10001	5	0 765	1	2011/2012	10	2011-10-16	8342	9991	2	0 766	1	2011/2012	10	2011-10-16	8635	9985	5	0 767	1	2011/2012	10	2011-10-15	9997	9993	2	4 768	1	2011/2012	10	2011-10-15	10000	9989	1	2 769	1	2011/2012	10	2011-10-15	1773	10001	1	1 770	1	2011/2012	10	2011-10-15	9994	9987	3	1 771	1	2011/2012	10	2011-10-15	8203	9984	1	2 772	1	2011/2012	10	2011-10-15	9998	8571	3	1 773	1	2011/2012	11	2011-10-23	9985	10000	1	0 774	1	2011/2012	11	2011-10-22	9987	9998	2	0 775	1	2011/2012	11	2011-10-22	9991	9994	3	1 776	1	2011/2012	11	2011-10-22	9993	8203	2	2 777	1	2011/2012	11	2011-10-23	8571	8342	2	1 778	1	2011/2012	11	2011-10-22	9989	9997	0	2 779	1	2011/2012	11	2011-10-23	10001	8635	1	2 780	1	2011/2012	11	2011-10-22	9984	1773	2	0 781	1	2011/2012	12	2011-10-29	8342	9987	4	5 782	1	2011/2012	12	2011-10-30	8635	9989	4	0 783	1	2011/2012	12	2011-10-30	9997	9985	1	1 784	1	2011/2012	12	2011-10-29	10000	9984	1	1 785	1	2011/2012	12	2011-10-29	1773	9993	3	2 786	1	2011/2012	12	2011-10-29	9994	8571	1	4 787	1	2011/2012	12	2011-10-29	8203	10001	1	0 788	1	2011/2012	12	2011-10-29	9998	9991	1	1 789	1	2011/2012	13	2011-11-06	9985	8342	2	1 790	1	2011/2012	13	2011-11-05	9987	8571	2	2 791	1	2011/2012	13	2011-11-04	9991	8203	6	2 792	1	2011/2012	13	2011-11-05	9993	9998	2	0 793	1	2011/2012	13	2011-11-05	10000	9997	0	0 794	1	2011/2012	13	2011-11-05	9989	1773	3	1 795	1	2011/2012	13	2011-11-05	10001	9994	2	4 796	1	2011/2012	13	2011-11-06	9984	8635	1	0 797	1	2011/2012	14	2011-11-20	8342	9984	1	0 798	1	2011/2012	14	2011-11-20	8635	9997	3	1 799	1	2011/2012	14	2011-11-19	9987	10001	3	0 800	1	2011/2012	14	2011-11-18	1773	9985	1	3 801	1	2011/2012	14	2011-11-19	9994	9993	1	1 802	1	2011/2012	14	2011-11-19	8571	9991	0	4 803	1	2011/2012	14	2011-11-19	8203	10000	1	1 804	1	2011/2012	14	2011-11-19	9998	9989	2	1 805	1	2011/2012	15	2011-11-25	9985	8203	3	2 806	1	2011/2012	15	2011-11-27	9991	9987	2	0 807	1	2011/2012	15	2011-11-26	9993	8571	0	1 808	1	2011/2012	15	2011-11-26	9997	1773	2	1 809	1	2011/2012	15	2011-11-26	10000	8635	2	3 810	1	2011/2012	15	2011-11-26	9989	8342	0	1 811	1	2011/2012	15	2011-11-26	10001	9998	2	1 812	1	2011/2012	15	2011-11-26	9984	9994	1	1 813	1	2011/2012	16	2011-12-04	8635	1773	0	0 814	1	2011/2012	16	2011-12-04	9985	9991	0	0 815	1	2011/2012	16	2011-12-02	9993	9987	2	0 816	1	2011/2012	16	2011-12-03	9997	9998	2	3 817	1	2011/2012	16	2011-12-03	10000	9994	1	1 818	1	2011/2012	16	2011-12-03	9989	8203	2	2 819	1	2011/2012	16	2011-12-03	10001	8342	0	1 820	1	2011/2012	16	2011-12-03	9984	8571	1	2 821	1	2011/2012	17	2011-12-11	8342	9997	1	0 822	1	2011/2012	17	2011-12-10	9987	9989	4	0 823	1	2011/2012	17	2011-12-10	1773	10000	2	2 824	1	2011/2012	17	2011-12-10	9994	9985	1	1 825	1	2011/2012	17	2011-12-10	8571	10001	1	0 826	1	2011/2012	17	2011-12-09	8203	8635	2	1 827	1	2011/2012	17	2011-12-10	9998	9984	0	2 828	1	2011/2012	17	2011-12-10	9991	9993	0	1 829	1	2011/2012	18	2011-12-18	8635	9994	3	2 830	1	2011/2012	18	2011-12-18	9985	9987	0	0 831	1	2011/2012	18	2011-12-17	9997	8203	0	0 832	1	2011/2012	18	2011-12-17	10000	9998	2	3 833	1	2011/2012	18	2011-12-18	1773	8342	3	1 834	1	2011/2012	18	2011-12-17	9989	8571	2	0 835	1	2011/2012	18	2011-12-17	10001	9993	3	1 836	1	2011/2012	18	2011-12-18	9984	9991	0	1 837	1	2011/2012	19	2011-12-26	8342	10000	1	0 838	1	2011/2012	19	2011-12-26	9987	9984	4	2 839	1	2011/2012	19	2011-12-26	9991	10001	3	1 840	1	2011/2012	19	2011-12-26	9993	9989	0	0 841	1	2011/2012	19	2011-12-26	9994	9997	0	0 842	1	2011/2012	19	2011-12-26	8571	9985	2	0 843	1	2011/2012	19	2011-12-26	8203	1773	2	2 844	1	2011/2012	19	2011-12-27	9998	8635	1	1 845	1	2011/2012	2	2011-08-05	8635	8203	3	1 846	1	2011/2012	2	2011-08-07	9985	9994	3	1 847	1	2011/2012	2	2011-08-06	9993	9991	2	2 848	1	2011/2012	2	2011-08-07	9997	8342	3	3 849	1	2011/2012	2	2011-08-06	10000	1773	2	1 850	1	2011/2012	2	2011-08-06	9989	9987	0	0 851	1	2011/2012	2	2011-08-07	10001	8571	0	0 852	1	2011/2012	2	2011-08-06	9984	9998	1	1 853	1	2011/2012	20	2012-01-15	8635	8342	3	0 854	1	2011/2012	20	2012-01-14	9985	9993	6	1 855	1	2011/2012	20	2012-01-14	9997	8571	0	0 856	1	2011/2012	20	2012-01-15	10000	9987	1	1 Showing 100 out of 12837 rows CTEs with nested subqueries If you find yourself listing multiple subqueries in the FROM clause with nested statement, your query will likely become long, complex, and difficult to read. Since many queries are written with the intention of being saved and re-run in the future, proper organization is key to a seamless workflow. Arranging subqueries as CTEs will save you time, space, and confusion in the long run! Instructions 100 XP Declare a CTE that calculates the total goals from matches in August of the 2013/2014 season. Left join the CTE onto the league table using country_id from the match_list CTE. Filter the list on the inner subquery to only select matches in August of the 2013/2014 season. -- Set up your CTE
___ match_list ___ (
    SELECT 
  		country_id,
  	   (home_goal + away_goal) AS goals
    FROM match
  	-- Create a list of match IDs to filter data in the CTE
    WHERE id IN (
       SELECT ___
       FROM ___
       WHERE season = ___ AND EXTRACT(MONTH FROM ___) = ___))
-- Select the league name and average of goals in the CTE
SELECT 
	___,
    ___(___)
FROM league AS l
-- Join the CTE onto the league table
LEFT JOIN ___ ON l.id = match_list.___
GROUP BY l.name;