Fun with your Amazon order history

Querying Amazon Purchase data

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
);

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

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

Total money spent on Books:

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