<jupyter_start><jupyter_text>Answering Business Question using SQLIn this project we are using `chinook.db` which is a SQLite database that holds data for an online music store. A copy of the database schema is given below.<jupyter_code>import sqlite3
import pandas as pd

db = 'chinook.db'

#Helper function to run SQL queries that outputs a table (result set) as a dataframe from pandas
def run_query(q: str):
    with sqlite3.connect(db) as conn:
        return pd.read_sql(q, conn)

#Helper function to run SQL commands that do not require outputing a table (result set)
def run_command(c: str)->None:
    with sqlite3.connect(db) as conn:
        conn.isolation_level = None
        conn.execute(c)

#Helper function to check list tables and views in the databse
def show_tables():
    q = "SELECT name, type FROM sqlite_master WHERE type IN ('table','view');"
    return run_query(q)<jupyter_output><empty_output><jupyter_text>Let us quickly have a look of all the tables and views in the database.<jupyter_code>show_tables()<jupyter_output><empty_output><jupyter_text>Most sold genres in USA<jupyter_code>query = '''
WITH usa_track_count AS
    (
        SELECT 
            g.name,
            COUNT(t.track_id) as track_count
        FROM genre g
        LEFT JOIN track t ON g.genre_id = t.genre_id
        LEFT JOIN invoice_line il ON t.track_id = il.track_id
        LEFT JOIN invoice i ON il.invoice_id = i.invoice_id 
        LEFT JOIN customer c ON i.customer_id = c.customer_id
        WHERE c.country = 'USA'
        GROUP BY g.genre_id
        ORDER BY track_count DESC
    )
    
SELECT 
    *,
    CAST(track_count*100 AS FLOAT) / (SELECT SUM(track_count) FROM usa_track_count) track_percent 
FROM usa_track_count
LIMIT 10;
        
'''

run_query(query)<jupyter_output><empty_output><jupyter_text>Sales support agent performanceEach customer for the Chinook store gets assigned to a sales support agent within the company when they first make a purchase. We will 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>query = '''
SELECT 
    e.employee_id,
    e.first_name || " " || e.last_name employee_name,
    e.hire_Date,
    SUM(i.total) total_sales_amount
FROM employee e
LEFT JOIN customer c ON e.employee_id = c.support_rep_id
LEFT JOIN invoice i ON c.customer_id = i.customer_id
WHERE e.title = 'Sales Support Agent'
GROUP BY e.employee_id
ORDER BY total_sales_amount DESC;        
'''

run_query(query)<jupyter_output><empty_output><jupyter_text>As can be seen from the above result, the employee named **<NAME>** has the most sales in value. However, it is also apparent that they were hired approximately **1 month earlier** than **<NAME>** who is in the second position and has more sales amount than **<NAME>** (in third) who was hired almost **5 months later** than **Margaret**.There performance is clearly reflective of when they were hired.  Country-wise customer sales data<jupyter_code>query = '''

WITH customer_sales_metrics_by_country AS
    (SELECT 
        c1.country, 
        COUNT(DISTINCT c1.customer_id) customer_count,
        SUM(i1.total) total_sales_value,
        CAST((SUM(i1.total)) AS FLOAT) / CAST((COUNT(DISTINCT c1.customer_id)) AS FLOAT) avg_sales_value_per_customer,
        CAST((SUM(i1.total)) AS FLOAT) / CAST((COUNT(i1.invoice_id)) AS FLOAT) avg_order_value
    FROM customer c1
    LEFT JOIN invoice i1 ON c1.customer_id = i1.customer_id
    GROUP BY c1.country
    HAVING customer_count > 1

    UNION

    SELECT
        "Other" AS country,
        SUM(customer_count) customer_count,
        SUM(total_sales_value) total_sales_value,
        AVG(avg_sales_value_per_customer) avg_saleavg_sales_value_per_customers_value,
        AVG(avg_order_value) avg_order_value
    FROM (
        SELECT 
            COUNT(DISTINCT c2.customer_id) customer_count,
            SUM(i2.total) total_sales_value,
            CAST((SUM(i2.total)) AS FLOAT) / CAST((COUNT(DISTINCT c2.customer_id)) AS FLOAT) avg_sales_value_per_customer,
            CAST((SUM(i2.total)) AS FLOAT) / CAST((COUNT(i2.invoice_id)) AS FLOAT) avg_order_value
        FROM customer c2
        LEFT JOIN invoice i2 ON c2.customer_id = i2.customer_id
        GROUP BY c2.country
        HAVING customer_count < 2
    )

    ORDER BY total_sales_value DESC)

SELECT
    country,
    customer_count,
    total_sales_value,
    avg_sales_value_per_customer,
    avg_order_value
FROM
    (
    SELECT
        cc.*,
        CASE
            WHEN cc.country = "Other" THEN 1
            ELSE 0
        END AS sort
    FROM customer_sales_metrics_by_country cc
   )
ORDER BY sort ASC
'''

run_query(query)<jupyter_output><empty_output><jupyter_text>From the above data, it is apparent USA has the most customer with **13** and Canada is closest to them in second position with **8**. What's interesting here, although countries like Brazil (5 customers), Germany (4 customers), Czech Republic (2 customers), and United Kingdom (3 customers) haev lower total sales value than USA, their average sales value per customer is higher, which is of great potential for growth in the future.  A change in track purchasing strategy?The Chinook store is setup in a way that allows customer to make purchases in one of the two ways:- purchase a whole album- purchase a collection of one or more individual tracks.The store does not let customers purchase a whole album, and then add individual tracks to that same purchase (unless they do that by choosing each track manually). When customers purchase albums they are charged the same price as if they had purchased each of those tracks separately.Management are currently considering changing their purchasing strategy to save money. The strategy they are considering is to purchase only the most popular tracks from each album from record companies, instead of purchasing every track from an album.We have been asked to find out what percentage of purchases are individual tracks vs whole albums, so that management can use this data to understand the effect this decision might have on overall revenue.In order to answer the question, we're going to have to identify whether each invoice has all the tracks from an album. We can do this by getting the list of tracks from an invoice and comparing it to the list of tracks from an album. We can find the album to compare the purchase to by looking up the album that one of the purchased tracks belongs to. It doesn't matter which track we pick, since if it's an album purchase, that album will be the same for all tracks.<jupyter_code>query = '''

WITH types_of_invoices AS
    (
        SELECT 
            i.invoice_id,
            CASE 
                WHEN 
                    (
                        SELECT 
                            t1.track_id
                        FROM invoice i1
                        INNER JOIN invoice_line il1 ON i1.invoice_id = il1.invoice_id
                        INNER JOIN track t1 ON il1.track_id = t1.track_id
                        INNER JOIN album a1 ON t1.album_id = a1.album_id
                        WHERE i1.invoice_id = i.invoice_id

                        EXCEPT

                        SELECT 
                            t2.track_id
                        FROM track t2
                        INNER JOIN album a2 ON t2.album_id = a2.album_id
                        WHERE a2.album_id = a.album_id

                    ) IS NULL
                    AND
                    (
                        SELECT 
                            t3.track_id
                        FROM track t3
                        INNER JOIN album a3 ON t3.album_id = a3.album_id
                        WHERE a3.album_id = a.album_id

                        EXCEPT

                        SELECT 
                            t4.track_id
                        FROM invoice i4
                        INNER JOIN invoice_line il4 ON i4.invoice_id = il4.invoice_id
                        INNER JOIN track t4 ON il4.track_id = t4.track_id
                        INNER JOIN album a4 ON t4.album_id = a4.album_id
                        WHERE i4.invoice_id = i.invoice_id

                    ) IS NULL
                    THEN "Album Purchase"
                ELSE "Individual Track(s) Purchase"
            END purchase_type
        FROM invoice i
        INNER JOIN invoice_line il ON i.invoice_id = il.invoice_id
        INNER JOIN track t ON il.track_id = t.track_id
        INNER JOIN album a ON t.album_id = a.album_id
        GROUP BY i.invoice_id
    )

SELECT
    purchase_type,
    COUNT(purchase_type) as num_of_invoice,
    CAST (COUNT(purchase_type)*100 AS FLOAT) / CAST((SELECT COUNT(*) FROM types_of_invoices) AS FLOAT) percent_of_invoices
FROM types_of_invoices
GROUP BY purchase_type
ORDER BY percent_of_invoices DESC
'''

run_query(query)<jupyter_output><empty_output><jupyter_text>From the above result, it is apparent that customers are not buying whole albums from the store. Rather they are making individual track purchases (more than 81%). Therefore, it is advisable for management to follow along with their plans of purchasing only the most popular tracks from each album from record companies, instead of purchasing every track from an album.  Which artist is used in the most playlists?<jupyter_code>query = '''
SELECT 
    art.name,
    COUNT(DISTINCT pt.playlist_id) total_playlist_featured_in
FROM artist art
LEFT JOIN album a ON art.artist_id = a.artist_id
LEFT JOIN track t ON a.album_id = t.album_id
LEFT JOIN playlist_track pt ON t.track_id = pt.track_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
'''

run_query(query)<jupyter_output><empty_output><jupyter_text>How many tracks have been purchased vs not purchased?<jupyter_code>query = '''
SELECT 
    (SELECT COUNT(track_id) FROM track) total_tracks,
    COUNT(DISTINCT track_id) purchased_tracks,
    ((SELECT COUNT(track_id) FROM track) - COUNT(DISTINCT track_id)) not_purchased_tracks,
    CAST(COUNT(DISTINCT track_id) * 100 AS FLOAT) / CAST((SELECT COUNT(track_id) FROM track) AS FLOAT) percent_of_purchase
FROM invoice_line
'''

run_query(query)<jupyter_output><empty_output><jupyter_text>Is the range of tracks in the store reflective of their sales popularity?<jupyter_code>query = '''

WITH genre_stat AS
    (
        SELECT 
            g.name as genre_name,
            CAST(COUNT(DISTINCT t.track_id) * 100 AS FLOAT) / CAST ((SELECT COUNT(track_id) FROM track) AS FLOAT) percentage_of_total_available,
            CAST(COUNT(il.track_id) * 100 AS FLOAT) / CAST ((SELECT COUNT(track_id) FROM invoice_line) AS FLOAT) percentage_of_total_sold
        FROM track t
        LEFT JOIN invoice_line il ON il.track_id = t.track_id
        LEFT JOIN genre g ON t.genre_id = g.genre_id
        GROUP BY g.genre_id
        ORDER BY 2 DESC
    )

SELECT
    genre_name,
    (percentage_of_total_sold/percentage_of_total_available) ratio_of_sold_to_available
FROM genre_stat
ORDER BY 2 DESC
'''

run_query(query)<jupyter_output><empty_output><jupyter_text>The column `ratio_of_sold_to_available` from the above result set means the ratio of percentage of how many tracks in a genre are sold and percentage of how many of them are available in the store for purchase. The more the ratio is more than 1, the more popular it is compared to its availability in the store. The less the ratio is less than 1, the less popular it is compared to its availability in the store.The following genres are more popular (in order) compared to their availability in the store: - Easy Listening - Alternative - R&B/Soul - Rock - Electronic/Dance - Metal - Blues - Alternative & Punk Rest of the genres are less popular compared to their availability in the store.  Do protected vs non-protected media types have an effect on popularity?<jupyter_code>query = '''

WITH genre_stat AS
    (
        SELECT 
            CASE
                WHEN substr(mt.name, 1, 9) = "Protected" THEN "Protected"
                ELSE "Non_Protected"
            END media_type,
            CAST(COUNT(DISTINCT t.track_id) * 100 AS FLOAT) / CAST ((SELECT COUNT(track_id) FROM track) AS FLOAT) percentage_of_total_available,
            CAST(COUNT(il.track_id) * 100 AS FLOAT) / CAST ((SELECT COUNT(track_id) FROM invoice_line) AS FLOAT) percentage_of_total_sold
        FROM track t
        LEFT JOIN invoice_line il ON il.track_id = t.track_id
        LEFT JOIN media_type mt ON t.media_type_id = mt.media_type_id
        GROUP BY media_type
    )

SELECT
    media_type,
    (percentage_of_total_sold/percentage_of_total_available) ratio_of_sold_to_available
FROM genre_stat
ORDER BY 2 DESC
'''

run_query(query)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.058652865992315534, 'fraction_numerical': 0.008703834391907786, 'mean_word_length': 2.8128550074738414, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 46, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '406126', 'license': 'MIT', 'n_tokens_mistral': 3926, 'n_tokens_neox': 3624, 'n_words': 1380, 'path': 'SQL_Projects/Answering_Business_Question_Using_SQL/Answering_Business_Question_Using_SQL.ipynb', 'repo_name': 'musrah13/data-engg-works', 'size': '12773'}