SQRL Demo

SQRL Script

IMPORT time.endOfMonth;
IMPORT time.epochToTimestamp;

IMPORT ecommerce-data.Customer TIMESTAMP epochToTimestamp(lastUpdated) AS updateTime;
IMPORT ecommerce-data.Product TIMESTAMP _ingest_time - INTERVAL 365 DAYS AS updateTime;
IMPORT ecommerce-data.Orders;

Customer := DISTINCT Customer ON customerid ORDER BY updateTime DESC;
Category := SELECT DISTINCT category AS name FROM Product;
Product := DISTINCT Product ON productid ORDER BY updateTime DESC;

-- Compute useful statistics on orders
Orders.entries.discount := coalesce(discount, 0.0);
Orders.entries.total := quantity * unit_price - discount;
Orders.total := SELECT sum(e.total) AS price, sum(e.discount) AS discount, count(1) AS num FROM @.entries e;

-- Relate Customer to Orders and categories to products
Customer.orders := JOIN Orders ON Orders.customerid = @.customerid;
Category.products := JOIN Product ON @.name = Product.category;

-- Aggregate all products the customer has ordered for the 'order again' feature
Orders.entries.product := JOIN Product ON Product.productid = @.productid;

Customer.order_again := SELECT e.productid, sum(e.quantity) AS quantity, count(1) AS num_orders, max(o.time) as most_recent
                            FROM @.orders o JOIN o.entries e
                            GROUP BY e.productid ORDER BY most_recent DESC, quantity DESC;

-- Aggregate customer spending by month and product category for the 'spending history' feature
Customer._spending_by_month_category :=
                     SELECT endOfMonth(o.time) AS month,
                            p.category AS category,
                            sum(e.total) AS total,
                            sum(e.discount) AS savings
                     FROM @.orders o JOIN o.entries e JOIN e.product p
                     GROUP BY month, category ORDER BY month DESC;

GraphQL Schema

type Category {
  name: String!
  products(productid: Int, name: String, description: String, category: String, updatetime: String): [Product!]
}

type Customer {
  customerid: Int!
  email: String!
  name: String!
  lastupdated: Int!
  updatetime: String!
  orders(id: Int, customerid: Int, time: String): [Orders!]
  order_again(productid: Int, quantity: Int, num_orders: Int, most_recent: String): [order_again!]
}

type Orders {
  id: Int!
  customerid: Int!
  time: String!
  entries(productid: Int, quantity: Int, unit_price: Float, discount: Float, total: Float): [entries!]
  total: total
}

type Product {
  productid: Int!
  name: String!
  description: String!
  category: String!
  updatetime: String!
}

type Query {
  Category(name: String): [Category!]
  Customer(customerid: Int, email: String, name: String, lastupdated: Int, updatetime: String): [Customer!]
  Orders(id: Int, customerid: Int, time: String): [Orders!]
  Product(productid: Int, name: String, description: String, category: String, updatetime: String): [Product!]
}

type entries {
  productid: Int!
  quantity: Int!
  unit_price: Float!
  parent: Orders!
  discount: Float!
  total: Float!
  product(productid: Int, name: String, description: String, category: String, updatetime: String): [Product!]
}

type order_again {
  productid: Int!
  quantity: Int!
  num_orders: Int!
  most_recent: String!
  parent: Customer!
}

type total {
  price: Float!
  discount: Float!
  num: Int!
  parent: Orders!
}

GraphQL Query