<jupyter_start><jupyter_text>Based on the data above, the top genres that are sold in America are Metal, Jazz, R&B/Soul, and Blues.  Using these facts, I recommend marketing SLim Jim Bites, Regal and Red Tone.<jupyter_code>q = "WITH custinv as (SELECT c.customer_id id, SUM(inv.total) price,\
    support_rep_id srid, COUNT(c.customer_id) clients FROM customer c\
    INNER JOIN invoice inv ON inv.customer_id=c.customer_id\
    GROUP BY id)\
    SELECT e.first_name ||' '||e.last_name RepName, e.hire_date,e.birthdate,e.city,SUM(custinv.price) total_sales,\
    custinv.clients FROM custinv\
    LEFT JOIN employee e on e.employee_id=custinv.srid\
    GROUP BY RepName;"
run_query(q)

rep_sales = run_query(q)
rep_sales.plot.bar('RepName','total_sales')<jupyter_output><empty_output><jupyter_text>The total sales for each rep is similar. Jane has the most sales, which is most likely because she has been there the longest, which means her cumulatie total sales have a higher probability of being larger than here peers.<jupyter_code>c='''
CREATE VIEW cs AS
    SELECT
        country,
        COUNT(*) cust
    FROM customer c
    GROUP BY 1
    ORDER BY 2 DESC;         
'''
run_command(c)

q='''
WITH ci as 
    (SELECT c.country, SUM(inv.total) sales, 
    COUNT(DISTINCT(c.customer_id)) cust_count, COUNT(inv.invoice_id) orders
    FROM invoice inv
    INNER JOIN customer c ON c.customer_id = inv.customer_id
    GROUP BY 1
    ORDER BY 2 DESC)
    
SELECT countryname, SUM(cs.cust) cust_count, 
    SUM(ci.sales) as total, SUM(ci.sales)/SUM(cs.cust) Sales_per_cust, SUM(ci.sales)/SUM(ci.orders) Avg_order_val
    FROM
        (
        SELECT cs.*,
        CASE
            WHEN cs.cust =1 THEN 'OTHER'
            ELSE country
        END AS countryname,
        CASE
            WHEN cs.cust =1 THEN 1
            ELSE 0
        END AS sort
        FROM cs) cs
    INNER JOIN ci ON ci.country = cs.country
    GROUP BY countryname
    ORDER BY sales DESC,sort ASC
'''
run_query(q)

salesbycountry = run_query(q)
salesbycountry.plot.bar('countryname','Sales_per_cust')
salesbycountry.plot.bar('countryname','Avg_order_val')<jupyter_output><empty_output><jupyter_text>The data is interesting because we have higher average order sales and sales per customer in the Czech Republic, but it has one of the lowest customer counts.  We can't take those values at face value beacuse the sample size is so small, its a imbalanced class issue for those.<jupyter_code>#This query is teh solution query, slightly too difficult for me at this time
albums_vs_tracks = '''
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(albums_vs_tracks)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.06825775656324583, 'fraction_numerical': 0.005011933174224344, 'mean_word_length': 1.932820153953814, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 9, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '250371', 'license': 'Apache-2.0', 'n_tokens_mistral': 1350, 'n_tokens_neox': 1226, 'n_words': 384, 'path': 'SQL/Guided Project_ Answering Business Questions using SQL/.ipynb_checkpoints/Basics-checkpoint.ipynb', 'repo_name': 'djmor12/Metis', 'size': '4190'}