Querying Amazon Purchase data

#sqlite, amazon

Published: Sep 10, 2018

A fun little exercise to query your amazon.com purchases.

Create an order history report using instructions here.

Load CSV into SQLite:

sqlite> .mode csv
sqlite> .import ./01-Jan-2009_to_11-Sep-2018.csv orders

Table schema

sqlite> .sch
CREATE TABLE orders(
  "Order Date" TEXT,
  "Order ID" TEXT,
  "Title" TEXT,
  "Category" TEXT,
  "ASIN/ISBN" TEXT,
  "UNSPSC Code" TEXT,
  "Website" TEXT,
  "Release Date" TEXT,
  "Condition" TEXT,
  "Seller" TEXT,
  "Seller Credentials" TEXT,
  "List Price Per Unit" TEXT,
  "Purchase Price Per Unit" TEXT,
  "Quantity" TEXT,
  "Payment Instrument Type" TEXT,
  "Purchase Order Number" TEXT,
  "PO Line Number" TEXT,
  "Ordering Customer Email" TEXT,
  "Shipment Date" TEXT,
  "Shipping Address Name" TEXT,
  "Shipping Address Street 1" TEXT,
  "Shipping Address Street 2" TEXT,
  "Shipping Address City" TEXT,
  "Shipping Address State" TEXT,
  "Shipping Address Zip" TEXT,
  "Order Status" TEXT,
  "Carrier Name & Tracking Number" TEXT,
  "Item Subtotal" TEXT,
  "Item Subtotal Tax" TEXT,
  "Item Total" TEXT,
  "Tax Exemption Applied" TEXT,
  "Tax Exemption Type" TEXT,
  "Exemption Opt-Out" TEXT,
  "Buyer Name" TEXT,
  "Currency" TEXT,
  "Group Name" TEXT
);

Query for all the books (the ISBNs do not start with letter B):

select "Order Date", "Title", "ASIN/ISBN", "Purchase Price Per Unit" from orders where "ASIN/ISBN" NOT LIKE "B%";

Total money spent on Books:

select  SUM(CAST("QUANTITY" AS DECIMAL) * CAST(SUBSTR("Purchase Price Per Unit",2) AS DECIMAL)) from orders where "ASIN/ISBN" NOT LIKE "B%";

I’d published the above section on dev.to.


Create a book table with only the data about books:

-- lets create the books table from the orders data
CREATE TABLE books AS SELECT "ASIN/ISBN", "Title", "Order Date",  "Purchase Price Per Unit" FROM orders;
ALTER TABLE books ADD COLUMN cat TEXT;
ALTER TABLE books ADD COLUMN subcat TEXT;
ALTER TABLE books ADD COLUMN author TEXT;
ALTER TABLE books ADD COLUMN summary TEXT;
ALTER TABLE books ADD COLUMN review TEXT;
ALTER TABLE books ADD COLUMN read_date TEXT;
CREATE TABLE books(
  id integer primary key,
  isbn text,
  title text,
  order_date text,
  price decimal,
  cat text,
  subcat text,
  author text,
  summary text,
  review text,
  read_date text
);
INSERT INTO books (isbn, title, order_date, price)
  SELECT DISTINCT "ASIN/ISBN", "Title", "Order Date",  CAST(SUBSTR("Purchase Price Per Unit",2) AS DECIMAL)
  FROM orders where "ASIN/ISBN" NOT LIKE "B%";