Strata Scratch Problem Sets

Problem sets, exercises, and educational guides for SQL and python. These exercises are specifically tailored for business and marketing analytics students and novices.

Back to the Strata Scratch website
Login to Strata Scratch

Basic SQL Exercises 2 (with Solutions)

Instructions

Questions

1. List all companies working in finacials sector which are headquarted in Europe or Asia.

Schema: datasets

Table: forbes_global_2010_2014

Hints:

SELECT
    company
FROM datasets.forbes_global_2010_2014
WHERE 
    (continent = 'Asia' OR continent = 'Europe') AND
    (sector = 'Financials')

2. Find the most profitable company from the financials sector in the entire world. What continent is it from?

Schema: datasets

Table: forbes_global_2010_2014

Hint:

SELECT
    company,
    continent
FROM datasets.forbes_global_2010_2014
WHERE 
    sector = 'Financials'
ORDER BY 
    profits DESC
LIMIT 1

3. For each sector find the maximum market value and order the sectors by it. Which sector is it best to invest in?

Schema: datasets

Table: forbes_global_2010_2014

Hints:

SELECT
    sector,
    max(marketvalue) AS max_marketvalue
FROM datasets.forbes_global_2010_2014
GROUP BY 
    sector
ORDER BY 
    max_marketvalue DESC

4. How are companies distributed among countries considering only the best sector from previous question?

Schema: datasets

Tables: forbes_global_2010_2014

Hints:

SELECT
    country,
    count(*) AS n_companies
FROM datasets.forbes_global_2010_2014
WHERE
    sector = 'Information Technology'
GROUP BY 
    country
ORDER BY
    n_companies DESC

5. Which industry shows profit on average while having the lowest sales of all industries?

Schema: datasets

Table: forbes_global_2010_2014

Hints:

SELECT
    industry
FROM
    datasets.forbes_global_2010_2014
GROUP BY 
    industry
HAVING 
    avg(profits) > 0
ORDER BY 
    min(sales) ASC
LIMIT 1

6. How many users speak English, German, French or Spanish?

Schema: datasets Table: playbook_users

Hints:

SELECT
    count(*) AS n_wanted_speakers
FROM datasets.playbook_users
WHERE
    language IN ('english', 'german', 'french', 'spanish')

7. Find the id of companies which have more than 10 users which are not speaking English, German, French or Spanish.

Schema: datasets

Table: playbook_users

Hints:

SELECT
    company_id
FROM datasets.playbook_users
WHERE
    language NOT IN ('english', 'german', 'french', 'spanish')
GROUP BY
    company_id
HAVING (count(*)) > 10

Schema: datasets

Table: playbook_users

Hints:

SELECT
    language,
    count(*) AS n_speakers
FROM datasets.playbook_users
GROUP BY 
    language
ORDER BY 
    n_speakers DESC

9. Find the company with a highest number of users which has a difference of more than 365 days between first and last activation dates.

Schema: datasets

Table: playbook_users

Hints:

SELECT
    company_id,

    count(user_id) AS n_users
FROM
    datasets.playbook_users
GROUP BY
    company_id
HAVING
    max(activated_at) - min(activated_at) >= 365
ORDER BY 
    n_users DESC
LIMIT 1

10. What is the language breakdown for the company from previous question?

Schema: datasets Table: playbook_users

Hints:

SELECT
    language,
    
    count(*) AS n_speakers
FROM
    datasets.playbook_users
WHERE 
    company_id = 1
GROUP BY
    language