<jupyter_start><jupyter_text>Finding the most popular genre in the USA:<jupyter_code># Creating the query:
popular_genres_in_usa = '''
WITH selecting_tracks_from_USA AS (
    SELECT il.*
    FROM InvoiceLine il
    INNER JOIN Invoice i ON i.InvoiceId = il.InvoiceId
    INNER JOIN Customer c ON c.CustomerId = i.CustomerId
    WHERE c.Country = 'USA')
           
SELECT 
    g.Name genre,
    COUNT(usa.InvoiceLineId) tracks_sold,
    CAST(COUNT(usa.InvoiceLineId) AS Float) / (SELECT COUNT(*) FROM selecting_tracks_from_USA) percentage_sold
FROM selecting_tracks_from_USA usa
INNER JOIN Track t ON t.TrackId = usa.TrackId
INNER JOIN Genre g ON g.GenreId = t.GenreId
GROUP BY g.Name
ORDER BY tracks_sold DESC
LIMIT 15;
'''

# Displaying the query in a table:
print('Displaying the most popular genre in the USA:')
run_query(popular_genres_in_usa)
# Saving query in a varaible:
genre_sales_usa = run_query(popular_genres_in_usa)

# Setting the index label.
genre_sales_usa.set_index("genre", inplace=True)

# Creating barh plot:
genre_sales_usa['tracks_sold'].plot.barh(title='Top Selling Genres in USA')
plt.ylabel('')
plt.show()<jupyter_output><empty_output><jupyter_text>The 3 artists the will have the most success are Red Tone, Slim Jim Bites and Meteor and The Girls. However, combining the 3 artists together, they do not come close to the size of the market that Rock has.  Evaluating employee performance:<jupyter_code># Creating the query:
employee_performance = '''
WITH only_sales_support_agents AS (
    SELECT *
    FROM Employee e
    WHERE e.Title = 'Sales Support Agent')
    
SELECT
    ossa.FirstName || ' ' || ossa.LastName employee_name,
    ossa.HireDate hiring_date,
    COUNT(c.SupportRepId) total_sales,
    SUM(i.total) total_value_of_sales
FROM only_sales_support_agents ossa
INNER JOIN Customer c ON c.SupportRepId = ossa.EmployeeId
INNER JOIN Invoice i ON i.CustomerId = c.CustomerId
GROUP BY employee_name;
'''

# Displaying the query in a table:
print('Displaying all Sales Support Agents:')
run_query(employee_performance)
# Saving query in a varaible:
performance_of_employee = run_query(employee_performance)

# Setting the index label.
performance_of_employee.set_index("employee_name", inplace=True)

# Sorting the values from high to low:
performance_of_employee.sort_values('total_sales', inplace=True)

# Creating barh plot:
performance_of_employee['total_sales'].plot.barh(title='Sales Support Agent Performance:')
plt.ylabel('')
plt.show()<jupyter_output><empty_output><jupyter_text>It looks that all 3 employees are performing similarly to each other; however, <NAME> who has the least amount of sales is also the last person to have been hired.  Analyzing the sales data for customers from each different country:<jupyter_code>creating_others = '''
SELECT 
    CASE WHEN (SELECT COUNT(Country) FROM Customer c WHERE c.Country = Country) = 1 THEN 'Other'
        ELSE c.Country
    END AS country,
    c.CustomerId customer,
    SUM(i.Total) total,
    SUM(il.UnitPrice) / COUNT(DISTINCT c.CustomerId) averages_sales_per_customer,
    SUM(il.UnitPrice) / COUNT(DISTINCT il.InvoiceId) average_order_value
FROM Invoice i
INNER JOIN Customer c ON c.CustomerId = i.CustomerId
INNER JOIN InvoiceLine il ON il.InvoiceId = i.InvoiceId
GROUP BY country
'''

run_query(creating_others)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08154121863799284, 'fraction_numerical': 0.0017921146953405018, 'mean_word_length': 5.0451263537906135, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 14, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '634089', 'license': 'MIT', 'n_tokens_mistral': 1157, 'n_tokens_neox': 1052, 'n_words': 336, 'path': '.ipynb_checkpoints/Answering Business Questions using SQL-checkpoint.ipynb', 'repo_name': 'CJL89/Answering-Business-Questions-using-SQL', 'size': '3355'}