Number of Steem User Accounts by Country [by @ekonugraha]

CREATE TABLE #Country (
	CountryName VARCHAR(50)
);

INSERT INTO #country (CountryName) VALUES ('Afghanistan');
INSERT INTO #country (CountryName) VALUES ('Albania');
INSERT INTO #country (CountryName) VALUES ('Algeria');
INSERT INTO #country (CountryName) VALUES ('American Samoa');
INSERT INTO #country (CountryName) VALUES ('Andorra');
INSERT INTO #country (CountryName) VALUES ('Angola');
INSERT INTO #country (CountryName) VALUES ('Anguilla');
INSERT INTO #country (CountryName) VALUES ('Antigua and Barbuda');
INSERT INTO #country (CountryName) VALUES ('Argentina');
INSERT INTO #country (CountryName) VALUES ('Armenia');
INSERT INTO #country (CountryName) VALUES ('Aruba');
INSERT INTO #country (CountryName) VALUES ('Australia');
INSERT INTO #country (CountryName) VALUES ('Austria');
INSERT INTO #country (CountryName) VALUES ('Aland Islands');
INSERT INTO #country (CountryName) VALUES ('Azerbaijan');
INSERT INTO #country (CountryName) VALUES ('Bahamas');
INSERT INTO #country (CountryName) VALUES ('Bahrain');
INSERT INTO #country (CountryName) VALUES ('Bangladesh');
INSERT INTO #country (CountryName) VALUES ('Barbados');
INSERT INTO #country (CountryName) VALUES ('Belarus');
INSERT INTO #country (CountryName) VALUES ('Belgium');
INSERT INTO #country (CountryName) VALUES ('Belize');
INSERT INTO #country (CountryName) VALUES ('Benin');
INSERT INTO #country (CountryName) VALUES ('Bermuda');
INSERT INTO #country (CountryName) VALUES ('Bhutan');
INSERT INTO #country (CountryName) VALUES ('Bolivia');
INSERT INTO #country (CountryName) VALUES ('Bosnia and Herzegovina');
INSERT INTO #country (CountryName) VALUES ('Botswana');
INSERT INTO #country (CountryName) VALUES ('Brazil');
INSERT INTO #country (CountryName) VALUES ('British Virgin Islands');
INSERT INTO #country (CountryName) VALUES ('Brunei');
INSERT INTO #country (CountryName) VALUES ('Bulgaria');
INSERT INTO #country (CountryName) VALUES ('Burkina Faso');
INSERT INTO #country (CountryName) VALUES ('Burundi');
INSERT INTO #country (CountryName) VALUES ('Cambodia');
INSERT INTO #country (CountryName) VALUES ('Cameroon');
INSERT INTO #country (CountryName) VALUES ('Canada');
INSERT INTO #country (CountryName) VALUES ('Cape Verde');
INSERT INTO #country (CountryName) VALUES ('Cayman Islands');
INSERT INTO #country (CountryName) VALUES ('Central African Republic');
INSERT INTO #country (CountryName) VALUES ('Chad');
INSERT INTO #country (CountryName) VALUES ('China');
INSERT INTO #country (CountryName) VALUES ('Christmas Island');
INSERT INTO #country (CountryName) VALUES ('Cocos Islands');
INSERT INTO #country (CountryName) VALUES ('Colombia');
INSERT INTO #country (CountryName) VALUES ('Comoros');
INSERT INTO #country (CountryName) VALUES ('Congo');
INSERT INTO #country (CountryName) VALUES ('Cook Islands');
INSERT INTO #country (CountryName) VALUES ('Costa Rica');
INSERT INTO #country (CountryName) VALUES ('Côte Ivoire');
INSERT INTO #country (CountryName) VALUES ('Crimea');
INSERT INTO #country (CountryName) VALUES ('Croatia');
INSERT INTO #country (CountryName) VALUES ('Cuba');
INSERT INTO #country (CountryName) VALUES ('Curaçao');
INSERT INTO #country (CountryName) VALUES ('Cyprus');
INSERT INTO #country (CountryName) VALUES ('Czech');
INSERT INTO #country (CountryName) VALUES ('Denmark');
INSERT INTO #country (CountryName) VALUES ('Djibouti');
INSERT INTO #country (CountryName) VALUES ('Dominica');
INSERT INTO #country (CountryName) VALUES ('East Timor');
INSERT INTO #country (CountryName) VALUES ('Ecuador');
INSERT INTO #country (CountryName) VALUES ('Egypt');
INSERT INTO #country (CountryName) VALUES ('El Salvador');
INSERT INTO #country (CountryName) VALUES ('Equatorial Guinea');
INSERT INTO #country (CountryName) VALUES ('Eritrea');
INSERT INTO #country (CountryName) VALUES ('Estonia');
INSERT INTO #country (CountryName) VALUES ('Ethiopia');
INSERT INTO #country (CountryName) VALUES ('Falkland Islands');
INSERT INTO #country (CountryName) VALUES ('Faroe Islands');
INSERT INTO #country (CountryName) VALUES ('Fiji');
INSERT INTO #country (CountryName) VALUES ('Finland');
INSERT INTO #country (CountryName) VALUES ('France');
INSERT INTO #country (CountryName) VALUES ('French Polynesia');
INSERT INTO #country (CountryName) VALUES ('Gabon');
INSERT INTO #country (CountryName) VALUES ('The Gambia');
INSERT INTO #country (CountryName) VALUES ('Georgia');
INSERT INTO #country (CountryName) VALUES ('German');
INSERT INTO #country (CountryName) VALUES ('Ghana');
INSERT INTO #country (CountryName) VALUES ('Gibraltar');
INSERT INTO #country (CountryName) VALUES ('Greece');
INSERT INTO #country (CountryName) VALUES ('Greenland');
INSERT INTO #country (CountryName) VALUES ('Grenada');
INSERT INTO #country (CountryName) VALUES ('Guadeloupe');
INSERT INTO #country (CountryName) VALUES ('Guam');
INSERT INTO #country (CountryName) VALUES ('Guatemala');
INSERT INTO #country (CountryName) VALUES ('Guernsey');
INSERT INTO #country (CountryName) VALUES ('Guinea');
INSERT INTO #country (CountryName) VALUES ('Guinea-Bissau');
INSERT INTO #country (CountryName) VALUES ('Guyana');
INSERT INTO #country (CountryName) VALUES ('Haiti');
INSERT INTO #country (CountryName) VALUES ('Honduras');
INSERT INTO #country (CountryName) VALUES ('Hong Kong');
INSERT INTO #country (CountryName) VALUES ('Hungary');
INSERT INTO #country (CountryName) VALUES ('Iceland');
INSERT INTO #country (CountryName) VALUES ('India');
INSERT INTO #country (CountryName) VALUES ('Indonesia');
INSERT INTO #country (CountryName) VALUES ('Iran');
INSERT INTO #country (CountryName) VALUES ('Iraq');
INSERT INTO #country (CountryName) VALUES ('Ireland');
INSERT INTO #country (CountryName) VALUES ('Isle of Man');
INSERT INTO #country (CountryName) VALUES ('Israel');
INSERT INTO #country (CountryName) VALUES ('Italy');
INSERT INTO #country (CountryName) VALUES ('Jamaica');
INSERT INTO #country (CountryName) VALUES ('Japan');
INSERT INTO #country (CountryName) VALUES ('Jersey');
INSERT INTO #country (CountryName) VALUES ('Jordan');
INSERT INTO #country (CountryName) VALUES ('Kazakhstan');
INSERT INTO #country (CountryName) VALUES ('Kenya');
INSERT INTO #country (CountryName) VALUES ('Kiribati');
INSERT INTO #country (CountryName) VALUES ('North Korea');
INSERT INTO #country (CountryName) VALUES ('South Korea');
INSERT INTO #country (CountryName) VALUES ('Kosovo');
INSERT INTO #country (CountryName) VALUES ('Kuwait');
INSERT INTO #country (CountryName) VALUES ('Kyrgyzstan');
INSERT INTO #country (CountryName) VALUES ('Laos');
INSERT INTO #country (CountryName) VALUES ('Latvia');
INSERT INTO #country (CountryName) VALUES ('Lebanon');
INSERT INTO #country (CountryName) VALUES ('Lesotho');
INSERT INTO #country (CountryName) VALUES ('Liberia');
INSERT INTO #country (CountryName) VALUES ('Libya');
INSERT INTO #country (CountryName) VALUES ('Liechtenstein');
INSERT INTO #country (CountryName) VALUES ('Lithuania');
INSERT INTO #country (CountryName) VALUES ('Luxembourg');
INSERT INTO #country (CountryName) VALUES ('Macedonia');
INSERT INTO #country (CountryName) VALUES ('Madagascar');
INSERT INTO #country (CountryName) VALUES ('Malawi');
INSERT INTO #country (CountryName) VALUES ('Malaysia');
INSERT INTO #country (CountryName) VALUES ('Maldives');
INSERT INTO #country (CountryName) VALUES ('Mali');
INSERT INTO #country (CountryName) VALUES ('Malta');
INSERT INTO #country (CountryName) VALUES ('Marshall Islands');
INSERT INTO #country (CountryName) VALUES ('Martinique');
INSERT INTO #country (CountryName) VALUES ('Mauritania');
INSERT INTO #country (CountryName) VALUES ('Mauritius');
INSERT INTO #country (CountryName) VALUES ('Mayotte');
INSERT INTO #country (CountryName) VALUES ('Mexico');
INSERT INTO #country (CountryName) VALUES ('Federated States of Micronesia');
INSERT INTO #country (CountryName) VALUES ('Moldova');
INSERT INTO #country (CountryName) VALUES ('Monaco');
INSERT INTO #country (CountryName) VALUES ('Mongolia');
INSERT INTO #country (CountryName) VALUES ('Montenegro');
INSERT INTO #country (CountryName) VALUES ('Montserrat');
INSERT INTO #country (CountryName) VALUES ('Morocco');
INSERT INTO #country (CountryName) VALUES ('Mozambique');
INSERT INTO #country (CountryName) VALUES ('Myanmar');
INSERT INTO #country (CountryName) VALUES ('Namibia');
INSERT INTO #country (CountryName) VALUES ('Nauru');
INSERT INTO #country (CountryName) VALUES ('Nepal');
INSERT INTO #country (CountryName) VALUES ('Netherland');
INSERT INTO #country (CountryName) VALUES ('New Caledonia');
INSERT INTO #country (CountryName) VALUES ('New Zealand');
INSERT INTO #country (CountryName) VALUES ('Nicaragua');
INSERT INTO #country (CountryName) VALUES ('Nigeria');
INSERT INTO #country (CountryName) VALUES ('Niue');
INSERT INTO #country (CountryName) VALUES ('Norfolk Island');
INSERT INTO #country (CountryName) VALUES ('Northern Mariana Islands');
INSERT INTO #country (CountryName) VALUES ('Norway');
INSERT INTO #country (CountryName) VALUES ('Oman');
INSERT INTO #country (CountryName) VALUES ('Pakistan');
INSERT INTO #country (CountryName) VALUES ('Palau');
INSERT INTO #country (CountryName) VALUES ('Palestine');
INSERT INTO #country (CountryName) VALUES ('Panama');
INSERT INTO #country (CountryName) VALUES ('Papua New Guinea');
INSERT INTO #country (CountryName) VALUES ('Paraguay');
INSERT INTO #country (CountryName) VALUES ('Peru');
INSERT INTO #country (CountryName) VALUES ('Philippines');
INSERT INTO #country (CountryName) VALUES ('Pitcairn Islands');
INSERT INTO #country (CountryName) VALUES ('Poland');
INSERT INTO #country (CountryName) VALUES ('Portugal');
INSERT INTO #country (CountryName) VALUES ('Puerto Rico');
INSERT INTO #country (CountryName) VALUES ('Qatar');
INSERT INTO #country (CountryName) VALUES ('Réunion');
INSERT INTO #country (CountryName) VALUES ('Romania');
INSERT INTO #country (CountryName) VALUES ('Russia');
INSERT INTO #country (CountryName) VALUES ('Rwanda');
INSERT INTO #country (CountryName) VALUES ('Sahrawi');
INSERT INTO #country (CountryName) VALUES ('Saint Barthélemy');
INSERT INTO #country (CountryName) VALUES ('Saint Helena');
INSERT INTO #country (CountryName) VALUES ('Ascension');
INSERT INTO #country (CountryName) VALUES ('Tristan da Cunha');
INSERT INTO #country (CountryName) VALUES ('Saint Kitts');
INSERT INTO #country (CountryName) VALUES ('Nevis');
INSERT INTO #country (CountryName) VALUES ('Saint Martin');
INSERT INTO #country (CountryName) VALUES ('Saint Lucia');
INSERT INTO #country (CountryName) VALUES ('Saint Pierre');
INSERT INTO #country (CountryName) VALUES ('Miquelon');
INSERT INTO #country (CountryName) VALUES ('Saint Vincent');
INSERT INTO #country (CountryName) VALUES ('Grenadines');
INSERT INTO #country (CountryName) VALUES ('Samoa');
INSERT INTO #country (CountryName) VALUES ('San Marino');
INSERT INTO #country (CountryName) VALUES ('São Tomé');
INSERT INTO #country (CountryName) VALUES ('Príncipe');
INSERT INTO #country (CountryName) VALUES ('Saudi Arabia');
INSERT INTO #country (CountryName) VALUES ('Senegal');
INSERT INTO #country (CountryName) VALUES ('Serbia');
INSERT INTO #country (CountryName) VALUES ('Seychelles');
INSERT INTO #country (CountryName) VALUES ('Sierra Leone');
INSERT INTO #country (CountryName) VALUES ('Singapore');
INSERT INTO #country (CountryName) VALUES ('Sint Maarten');
INSERT INTO #country (CountryName) VALUES ('Slovakia');
INSERT INTO #country (CountryName) VALUES ('Solomon Island');
INSERT INTO #country (CountryName) VALUES ('Somalia');
INSERT INTO #country (CountryName) VALUES ('South Africa');
INSERT INTO #country (CountryName) VALUES ('South Sudan');
INSERT INTO #country (CountryName) VALUES ('Spain');
INSERT INTO #country (CountryName) VALUES ('Sri Lanka');
INSERT INTO #country (CountryName) VALUES ('Sudan');
INSERT INTO #country (CountryName) VALUES ('Suriname');
INSERT INTO #country (CountryName) VALUES ('Svalbard');
INSERT INTO #country (CountryName) VALUES ('Swaziland');
INSERT INTO #country (CountryName) VALUES ('Sweden');
INSERT INTO #country (CountryName) VALUES ('Switzerland');
INSERT INTO #country (CountryName) VALUES ('Syria');
INSERT INTO #country (CountryName) VALUES ('Taiwan');
INSERT INTO #country (CountryName) VALUES ('Tajikistan');
INSERT INTO #country (CountryName) VALUES ('Tanzania');
INSERT INTO #country (CountryName) VALUES ('Thailand');
INSERT INTO #country (CountryName) VALUES ('Togo');
INSERT INTO #country (CountryName) VALUES ('Tokelau');
INSERT INTO #country (CountryName) VALUES ('Tonga');
INSERT INTO #country (CountryName) VALUES ('Trinidad');
INSERT INTO #country (CountryName) VALUES ('Tobago');
INSERT INTO #country (CountryName) VALUES ('Tunisia');
INSERT INTO #country (CountryName) VALUES ('Turkey');
INSERT INTO #country (CountryName) VALUES ('Turkmenistan');
INSERT INTO #country (CountryName) VALUES ('Turks');
INSERT INTO #country (CountryName) VALUES ('Caicos Island');
INSERT INTO #country (CountryName) VALUES ('Tuvalu');
INSERT INTO #country (CountryName) VALUES ('Uganda');
INSERT INTO #country (CountryName) VALUES ('Ukraine');
INSERT INTO #country (CountryName) VALUES ('United Arab Emirates');
INSERT INTO #country (CountryName) VALUES ('United Kingdom');
INSERT INTO #country (CountryName) VALUES ('United States');
INSERT INTO #country (CountryName) VALUES ('Virgin Island');
INSERT INTO #country (CountryName) VALUES ('Uruguay');
INSERT INTO #country (CountryName) VALUES ('Uzbekistan');
INSERT INTO #country (CountryName) VALUES ('Vanuatu');
INSERT INTO #country (CountryName) VALUES ('Vatican');
INSERT INTO #country (CountryName) VALUES ('Venezuela');
INSERT INTO #country (CountryName) VALUES ('Vietnam');
INSERT INTO #country (CountryName) VALUES ('Wallis');
INSERT INTO #country (CountryName) VALUES ('Futuna');
INSERT INTO #country (CountryName) VALUES ('Yemen');
INSERT INTO #country (CountryName) VALUES ('Zambia');
INSERT INTO #country (CountryName) VALUES ('Zimbabwe');



SELECT COUNT(DISTINCT a.username) as TotalUsers,CountryName FROM (
	SELECT name as username, CountryName FROM dbo.Accounts
	INNER JOIN #country ON dbo.Accounts.json_metadata LIKE '%' + #country.CountryName + '%'
	UNION
	SELECT u.username, CountryName FROM
	(
		SELECT author as username, CountryName from dbo.Comments
		INNER JOIN #country ON dbo.Comments.json_metadata LIKE '%introduceyourself%'
		WHERE depth=0 AND category = #country.CountryName
		GROUP BY author, CountryName
		UNION
		SELECT author as username, CountryName from dbo.Comments
		INNER JOIN #country ON dbo.Comments.json_metadata LIKE '%' + #country.CountryName + '%'
		WHERE depth=0 AND category = 'introduceyourself'
		GROUP BY author, CountryName
	) u
) a
GROUP BY CountryName
ORDER BY TotalUsers DESC