Project:
SQL - BrainTree SQL Coding Challenge Data Analyst.This is a Challenge create by Alexander Connely.
The first step was importing four CSV files into SQL:
The sequece was answer these questions:
Code Challenge v2.11
______________________________________________________________________________________________________________________________________________________________________
1. Data Integrity Checking & Cleanup
Alphabetically list all of the country codes in the continent_map table that appear more than once. Display any values where country_code is null as country_code = "FOO" and make this row appear first in the list, even though it should alphabetically sort to the middle. Provide the results of this query as your answer.
For all countries that have multiple rows in the continent_map table, delete all multiple records leaving only the 1 record per country. The record that you keep should be the first one when sorted by the continent_code alphabetically ascending. Provide the query/ies and explanation of step(s) that you follow to delete these records.
Link to SQL query - Answer 1
______________________________________________________________________________________________________________________________________________________________________ 2. List the countries ranked 10-12 in each continent by the percent of year-over-year growth descending from 2011 to 2012.
The percent of growth should be calculated as: ((2012 gdp - 2011 gdp) / 2011 gdp)
The list should include the columns:
rank
continent_name
country_code
country_name
growth_percent
Link to SQL query - Answer 2
______________________________________________________________________________________________________________________________________________________________________ 3. For the year 2012, create a 3 column, 1 row report showing the percent share of gdp_per_capita for the following regions:
(i) Asia, (ii) Europe, (iii) the Rest of the World. Your result should look something like
Asia Europe Rest of World
25.0% 25.0% 50.0%
Link to SQL query - Answer 3
______________________________________________________________________________________________________________________________________________________________________ 4a. What is the count of countries and sum of their related gdp_per_capita values for the year 2007 where the string 'an' (case insensitive) appears anywhere in the country name?
4b. Repeat question 4a, but this time make the query case sensitive.
Link to SQL query - Answer 4
______________________________________________________________________________________________________________________________________________________________________ 5. Find the sum of gpd_per_capita by year and the count of countries for each year that have non-null gdp_per_capita where (i) the year is before 2012 and (ii) the country has a null gdp_per_capita in 2012. Your result should have the columns:
year
country_count
total
Link to SQL query - Answer 5
______________________________________________________________________________________________________________________________________________________________________ 6. All in a single query, execute all of the steps below and provide the results as your final answer:
a. create a single list of all per_capita records for year 2009 that includes columns:
continent_name
country_code
country_name
gdp_per_capita
b. order this list by:
continent_name ascending
characters 2 through 4 (inclusive) of the country_name descending
c. create a running total of gdp_per_capita by continent_name
d. return only the first record from the ordered list for which each continent's running total of gdp_per_capita meets or exceeds $70,000.00 with the following columns:
continent_name
country_code
country_name
gdp_per_capita
running_total
Link to SQL query - Answer 6
______________________________________________________________________________________________________________________________________________________________________ 7. Find the country with the highest average gdp_per_capita for each continent for all years. Now compare your list to the following data set. Please describe any and all mistakes that you can find with the data set below. Include any code that you use to help detect these mistakes.
Link to SQL query - Answer 7
______________________________________________________________________________________________________________________________________________________________________
Extra
This is a Power Bi Report, where I imported directly from SQL Management Studio (query) to Power Bi.
This way I could show the formulas and results online from this SQL Data Analyst Challenge.
Link to Power Bi - Answers Report
* Just the first question I didn`t answer in the Power BI, because that was one ETL process.But you can check the process direct on the link Answer 1.
Programming Language: