notebook.community

Edit and run

Demonstrate queries in PostgreSQL (not using SQLAlchemy yet):





In [1]:



    


%load_ext sql



    











In [2]:



    


%sql postgresql://clinton:reece@localhost:5432/transactions



    


















    
Out[2]:








'Connected: clinton@transactions'

Data





In [3]:



    


%%sql
SELECT *
FROM customers;



    


















    






10 rows affected.










    
Out[3]:








    

        customer_id
        name
        email
        phone
        street
        city
        state
    

    

        ceb388a9-f124-f73d-613f-a6a92a169a8c
        Laraine Rios
        aluta1813@live.com
        188.013.8219
        292 Margaret Trace
        Elizabeth City
        NH
    

    

        0da2d776-273d-c232-b480-64c577e9cdae
        Zachery Rivera
        greengage2048@gmail.com
        (373) 957-4596
        339 San Jacinto Bypass
        Jefferson City
        TN
    

    

        8e102442-55eb-1d07-68b3-e2ffd5d4af4d
        Karlyn Anthony
        unspecific2001@live.com
        373-957-4596
        931 Stanton Viaduct
        Southgate
        MT
    

    

        d0103c8d-7d1a-ce85-16d8-8358d9017e03
        Scott Floyd
        bizen1939@gmail.com
        +1-(373)-957-4596
        997 Wallen Drung
        Muskego
        IA
    

    

        fd7759e2-b3bd-61e3-bf64-53e5ca2758af
        Rigoberto Frazier
        couplet1870@yahoo.com
        +1-(373)-957-4596
        63 Cleo Rand Alley
        Elmira
        MO
    

    

        ed61fb22-b173-49d8-4bfb-9215e3b4f01d
        Dannielle Espinoza
        detlev1961@yahoo.com
        1-373-957-4596
        113 Decatur Garden
        Charlottesville
        AR
    

    

        8173095d-d036-4fd4-675f-e85af652bfd5
        Jolanda Mccullough
        pediatric1847@live.com
        +1-(373)-957-4596
        979 Sfgh Access Freeway
        Sedalia
        MS
    

    

        6a7f5c71-ec97-ae7f-6cf2-3788b879c8f6
        Abel Shields
        mastoid1842@yandex.com
        1-373-957-4596
        699 Jessie East High Street
        Malden
        MI
    

    

        d6116d06-3862-690a-785b-dd6eb86b88b4
        Genaro Beach
        broddy1899@gmail.com
        188-013-8219
        84 Clementina High Street
        Vallejo
        WV
    

    

        c594ba42-8015-9c12-987a-3cf5f4a2cbc2
        Dylan Branch
        binman1997@gmail.com
        188.013.8219
        1186 Mary Hill
        Davis
        MI
    



















In [4]:



    


%%sql
SELECT *
FROM purchases;



    


















    






15 rows affected.










    
Out[4]:








    

        receipt_id
        customer_id
        date
        time
        isbn
        quantity
    

    

        0cdf1166-5fa6-0246-33b7-d1149d87a50a
        fd7759e2-b3bd-61e3-bf64-53e5ca2758af
        2013-05-25
        18:54:47
        1-21650-822-6
        4
    

    

        4b1040d5-f2c5-f462-19a7-a9e038370c75
        8173095d-d036-4fd4-675f-e85af652bfd5
        2011-08-06
        10:07:29
        1-32060-596-1
        1
    

    

        0cdf1166-5fa6-0246-33b7-d1149d87a50a
        d6116d06-3862-690a-785b-dd6eb86b88b4
        2013-01-07
        10:17:32
        1-32060-596-1
        4
    

    

        7d493d61-72f6-8042-7e50-f32c7fc04b70
        6a7f5c71-ec97-ae7f-6cf2-3788b879c8f6
        2013-08-09
        03:49:45
        1-38292-122-5
        4
    

    

        7d493d61-72f6-8042-7e50-f32c7fc04b70
        6a7f5c71-ec97-ae7f-6cf2-3788b879c8f6
        2014-11-11
        15:40:33
        1-32060-596-1
        1
    

    

        79523cbb-be6f-caba-0fe5-f8d62cbb43d7
        d0103c8d-7d1a-ce85-16d8-8358d9017e03
        2016-02-18
        14:20:05
        1-21650-822-6
        1
    

    

        592c2e53-e506-c5a9-eb3e-be8cd9f687f2
        8e102442-55eb-1d07-68b3-e2ffd5d4af4d
        2014-12-01
        08:26:02
        1-09229-682-4
        2
    

    

        e77d7f7e-2fb8-b56f-d4a9-ed97b550cf5f
        8173095d-d036-4fd4-675f-e85af652bfd5
        2014-03-29
        04:25:19
        1-21650-822-6
        4
    

    

        867873cb-9083-2eab-b88c-85c720fec36a
        8173095d-d036-4fd4-675f-e85af652bfd5
        2017-02-09
        21:46:09
        1-23378-527-4
        1
    

    

        f409efc0-3008-dde5-b68d-fc19d92e0362
        d0103c8d-7d1a-ce85-16d8-8358d9017e03
        2010-08-18
        18:00:14
        1-09229-682-4
        4
    

    

        e77d7f7e-2fb8-b56f-d4a9-ed97b550cf5f
        8e102442-55eb-1d07-68b3-e2ffd5d4af4d
        2014-11-29
        15:41:13
        1-21650-822-6
        5
    

    

        0cdf1166-5fa6-0246-33b7-d1149d87a50a
        d6116d06-3862-690a-785b-dd6eb86b88b4
        2013-06-18
        05:47:29
        1-21650-822-6
        3
    

    

        71187b0b-c4d2-70ee-0852-51dfc64d7065
        ed61fb22-b173-49d8-4bfb-9215e3b4f01d
        2011-08-08
        09:55:53
        1-09229-682-4
        2
    

    

        41fac398-0838-94b2-fc05-aa31e575163c
        d6116d06-3862-690a-785b-dd6eb86b88b4
        2017-05-29
        20:06:39
        1-38292-122-5
        4
    

    

        71187b0b-c4d2-70ee-0852-51dfc64d7065
        0da2d776-273d-c232-b480-64c577e9cdae
        2011-03-12
        21:54:08
        1-21650-822-6
        1
    



















In [5]:



    


%%sql
SELECT *
FROM books;



    


















    






5 rows affected.










    
Out[5]:








    

        isbn
        price
    

    

        1-23378-527-4
        40.79
    

    

        1-09229-682-4
        68.95
    

    

        1-32060-596-1
        10.00
    

    

        1-38292-122-5
        14.62
    

    

        1-21650-822-6
        53.50

Analysis

  • What is the total price of each purchase order?




In [6]:



    


%%sql
SELECT q.receipt_id,  
       SUM(q.quantity_price) AS total_price
FROM (SELECT p.receipt_id, 
             p.isbn,
             SUM(p.quantity) AS total_quantity,
             SUM(p.quantity)*b.price AS quantity_price
      FROM purchases p
           JOIN books b on p.isbn = b.isbn
      GROUP BY p.receipt_id, 
               p.isbn, 
               b.price
      ORDER BY p.receipt_id ASC, 
               total_quantity DESC) AS q
GROUP BY q.receipt_id;



    


















    






10 rows affected.










    
Out[6]:








    

        receipt_id
        total_price
    

    

        0cdf1166-5fa6-0246-33b7-d1149d87a50a
        414.50
    

    

        41fac398-0838-94b2-fc05-aa31e575163c
        58.48
    

    

        4b1040d5-f2c5-f462-19a7-a9e038370c75
        10.00
    

    

        592c2e53-e506-c5a9-eb3e-be8cd9f687f2
        137.90
    

    

        71187b0b-c4d2-70ee-0852-51dfc64d7065
        191.40
    

    

        79523cbb-be6f-caba-0fe5-f8d62cbb43d7
        53.50
    

    

        7d493d61-72f6-8042-7e50-f32c7fc04b70
        68.48
    

    

        867873cb-9083-2eab-b88c-85c720fec36a
        40.79
    

    

        e77d7f7e-2fb8-b56f-d4a9-ed97b550cf5f
        481.50
    

    

        f409efc0-3008-dde5-b68d-fc19d92e0362
        275.80
  • Which day of the week has the most book sales?




In [7]:



    


%%sql
SELECT CASE WHEN EXTRACT(DOW FROM date)=0 THEN 'Sun'
            WHEN EXTRACT(DOW FROM date)=1 THEN 'Mon'
            WHEN EXTRACT(DOW FROM date)=2 THEN 'Tue'
            WHEN EXTRACT(DOW FROM date)=3 THEN 'Wed'
            WHEN EXTRACT(DOW FROM date)=4 THEN 'Thu'
            WHEN EXTRACT(DOW FROM date)=5 THEN 'Fri'
            ELSE 'Sat'
       END AS day,
       count(*) AS count
FROM purchases
GROUP BY day
ORDER BY count DESC;



    


















    






6 rows affected.










    
Out[7]:








    

        day
        count
    

    

        Sat
        5
    

    

        Mon
        4
    

    

        Tue
        2
    

    

        Thu
        2
    

    

        Fri
        1
    

    

        Wed
        1
  • Who is the most frequent buyer?




In [8]:



    


%%sql
SELECT p.customer_id, 
       c.name,
       count(p.customer_id) AS count
FROM purchases p 
     JOIN customers c ON p.customer_id = c.customer_id
GROUP BY p.customer_id, 
         c.name
ORDER BY count DESC;



    


















    






8 rows affected.










    
Out[8]:








    

        customer_id
        name
        count
    

    

        d6116d06-3862-690a-785b-dd6eb86b88b4
        Genaro Beach
        3
    

    

        8173095d-d036-4fd4-675f-e85af652bfd5
        Jolanda Mccullough
        3
    

    

        d0103c8d-7d1a-ce85-16d8-8358d9017e03
        Scott Floyd
        2
    

    

        8e102442-55eb-1d07-68b3-e2ffd5d4af4d
        Karlyn Anthony
        2
    

    

        6a7f5c71-ec97-ae7f-6cf2-3788b879c8f6
        Abel Shields
        2
    

    

        0da2d776-273d-c232-b480-64c577e9cdae
        Zachery Rivera
        1
    

    

        ed61fb22-b173-49d8-4bfb-9215e3b4f01d
        Dannielle Espinoza
        1
    

    

        fd7759e2-b3bd-61e3-bf64-53e5ca2758af
        Rigoberto Frazier
        1
  • Which book has the most sales?




In [9]:



    


%%sql
SELECT p.isbn,
       SUM(p.quantity) AS total
FROM purchases p
     JOIN books b ON p.isbn = b.isbn
GROUP BY p.isbn
ORDER BY total DESC;



    


















    






5 rows affected.










    
Out[9]:








    

        isbn
        total
    

    

        1-21650-822-6
        18
    

    

        1-09229-682-4
        8
    

    

        1-38292-122-5
        8
    

    

        1-32060-596-1
        6
    

    

        1-23378-527-4
        1
  • Is the book price correlated to its quantity purchased?




In [10]:



    


%%sql
SELECT TRUNC(CORR(q.total_quantity, q.quantity_price)::numeric, 4) AS corr
FROM (SELECT p.receipt_id, 
             p.isbn,
             SUM(p.quantity) AS total_quantity,
             SUM(p.quantity)*b.price AS quantity_price
      FROM purchases p
           JOIN books b on p.isbn = b.isbn
      GROUP BY p.receipt_id, 
               p.isbn, 
               b.price
      ORDER BY p.receipt_id ASC, 
               total_quantity DESC) AS q;



    


















    






1 rows affected.










    
Out[10]:








    

        corr
    

    

        0.8584
  • Summarize the book prices.




In [11]:



    


%%sql
SELECT MIN(price), 
       ROUND(AVG(price), 2) AS avg, 
       MAX(price), 
       ROUND(STDDEV(price), 2) AS sd,
       ROUND(VARIANCE(price), 2) AS var
FROM books



    


















    






1 rows affected.










    
Out[11]:








    

        min
        avg
        max
        sd
        var
    

    

        10.00
        37.57
        68.95
        25.18
        633.91
  • Give the 2016 sales history.




In [12]:



    


%%sql
SELECT p.customer_id,
       c.name,
       p.date,
       p.isbn,
       p.quantity
FROM purchases p 
     JOIN customers c ON p.customer_id = c.customer_id
WHERE CAST(p.date AS TEXT) LIKE '2016%';



    


















    






1 rows affected.










    
Out[12]:








    

        customer_id
        name
        date
        isbn
        quantity
    

    

        d0103c8d-7d1a-ce85-16d8-8358d9017e03
        Scott Floyd
        2016-02-18
        1-21650-822-6
        1

Content source: clintonreece/sql-demos

Similar notebooks:

notebook.community | gallery | about