<jupyter_start><jupyter_text>--- Project: Answering Business Questions using SQL--- In this project we will be working with the 'chinook.db' database.__Imports & Base Functions__<jupyter_code>import numpy as np, pandas as pd, matplotlib.pyplot as plt
import sqlite3 
from matplotlib import cm
%matplotlib inline

db = 'chinook.db'

def run_query(q):
    with sqlite3.connect(db) as conn:
        return pd.read_sql(q, conn)

def run_command(command):
    with sqlite3.connect(db) as conn:
        conn.isolation_level = None
        conn.execute(command)

def show_tables():
    q = ''' 
    SELECT
        name,
        type
    FROM sqlite_master
    WHERE type IN ('table', 'view');
    '''
    return run_query(q)
show_tables()<jupyter_output><empty_output><jupyter_text>__Shortlist New Artists__The Chinook record store has just signed a deal with a new record label, and you've been tasked with selecting the first three albums that will be added to the store, from a list of four. All four albums are by artists that don't have any tracks in the store right now - we have the artist names, and the genre of music they produce:Artist Name	| GenreRegal	| Hip-HopRed Tone	| PunkMeteor and the Girls	| PopSlim Jim Bites	| Blues<jupyter_code>album_query = """
WITH usa_tracks_sold AS
    (SELECT il.* FROM invoice_line il
    INNER JOIN invoice i ON il.invoice_id = i.invoice_id
    INNER JOIN customer c ON i.customer_id = c.customer_id
    WHERE c.country = 'USA'
    )
    
SELECT 
    g.name genre,
    COUNT(uts.invoice_line_id) tracks_sold,
    CAST(COUNT(uts.invoice_line_id) AS FLOAT) / (SELECT COUNT (*) FROM usa_tracks_sold
    ) percentage_sold
FROM usa_tracks_sold uts
INNER JOIN track t ON t.track_id = uts.track_id
INNER JOIN genre g ON g.genre_id = t.genre_id
GROUP BY 1 
ORDER BY 2 DESC
LIMIT 10;
"""
run_query(album_query)
d = run_query(album_query).drop('tracks_sold', axis = 1)
d.plot(kind = 'bar', x = 'genre');<jupyter_output><empty_output><jupyter_text>While rock would be the best choice based on this data, choosing three from the four on the list above then the best option is to drop the hip-hop album from the shortlist.  __Sales Team Performance__Each customer for the Chinook store gets assigned to a sales support agent within the company when they first make a purchase. You have been asked to analyze the purchases of customers belonging to each employee to see if any sales support agent is performing either better or worse than the others.<jupyter_code>sales_perf_query = """
WITH customer_support_rep_sales AS
    (
    SELECT 
        i.customer_id,
        c.support_rep_id,
        SUM(i.total) total
    FROM invoice i
    INNER JOIN customer c ON i.customer_id = c.customer_id
    GROUP BY 1, 2
    )
    
SELECT 
    e.first_name ||' '|| e.last_name employee,
    e.hire_date,
    SUM(csrs.total) total_sales
FROM customer_support_rep_sales csrs
INNER JOIN employee e ON e.employee_id = csrs.support_rep_id
GROUP BY 1
"""

run_query(sales_perf_query)
run_query(sales_perf_query).plot(y = 'total_sales', 
                                 x = 'employee', 
                                 kind = 'bar');<jupyter_output><empty_output><jupyter_text>The variation in these three sales reps is roughly equivilant to the differences in their term of service with the company.__Sales By Country__<jupyter_code>sales_by_country = '''
WITH country_or_other AS
    (
     SELECT
       CASE
           WHEN (
                 SELECT count(*)
                 FROM customer
                 where country = c.country
                ) = 1 THEN "Other"
           ELSE c.country
       END AS country,
       c.customer_id,
       il.*
     FROM invoice_line il
     INNER JOIN invoice i ON i.invoice_id = il.invoice_id
     INNER JOIN customer c ON c.customer_id = i.customer_id
    )

SELECT
    country,
    customers,
    total_sales,
    average_order,
    customer_lifetime_value
FROM
    (
    SELECT
        country,
        count(distinct customer_id) customers,
        SUM(unit_price) total_sales,
        SUM(unit_price) / count(distinct customer_id) customer_lifetime_value,
        SUM(unit_price) / count(distinct invoice_id) average_order,
        CASE
            WHEN country = "Other" THEN 1
            ELSE 0
        END AS sort
    FROM country_or_other
    GROUP BY country
    ORDER BY sort ASC, total_sales DESC
    );
'''

run_query(sales_by_country)<jupyter_output><empty_output><jupyter_text>From the above data the UK, India and Czech Republic show potential for growth. That being said the sample sizes are very small at present.__Albums vs. Singles Sales__<jupyter_code>last_q = """
WITH invoice_first_track AS
    (
     SELECT
         il.invoice_id invoice_id,
         MIN(il.track_id) first_track_id
     FROM invoice_line il
     GROUP BY 1
    )

SELECT
    album_purchase,
    COUNT(invoice_id) number_of_invoices,
    CAST(count(invoice_id) AS FLOAT) / (
                                         SELECT COUNT(*) FROM invoice
                                      ) percent
FROM
    (
    SELECT
        ifs.*,
        CASE
            WHEN
                 (
                  SELECT t.track_id FROM track t
                  WHERE t.album_id = (
                                      SELECT t2.album_id FROM track t2
                                      WHERE t2.track_id = ifs.first_track_id
                                     ) 

                  EXCEPT 

                  SELECT il2.track_id FROM invoice_line il2
                  WHERE il2.invoice_id = ifs.invoice_id
                 ) IS NULL
             AND
                 (
                  SELECT il2.track_id FROM invoice_line il2
                  WHERE il2.invoice_id = ifs.invoice_id

                  EXCEPT 

                  SELECT t.track_id FROM track t
                  WHERE t.album_id = (
                                      SELECT t2.album_id FROM track t2
                                      WHERE t2.track_id = ifs.first_track_id
                                     ) 
                 ) IS NULL
             THEN "yes"
             ELSE "no"
         END AS "album_purchase"
     FROM invoice_first_track ifs
    )
GROUP BY album_purchase;
"""
run_query(last_q)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0625, 'fraction_numerical': 0.004315856777493606, 'mean_word_length': 2.142641888498242, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 11, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '218716', 'license': 'MIT', 'n_tokens_mistral': 1960, 'n_tokens_neox': 1810, 'n_words': 590, 'path': '09. Intermediate SQL/SQL_Business_Analytics.ipynb', 'repo_name': 'sciDelta/DataSci', 'size': '6256'}