dozer-config.yaml

cache_max_map_size: 2147483648
connections:
  - name: stic_conn
    config: !Postgres
      user: spikey
      password: sticai123
      host: localhost
      port: 5432
      database: postgres

sql: |
  SELECT 
    SUM(p.price * c.quantity) AS total_cart_value,
    5.32 AS shipping_est,
    9.45 AS tax_est,
    SUM(p.price * c.quantity) + 5.00 + 8.32 AS total_cart_value_with_tax,
    COUNT(*) AS total_items,
    5.32 * COUNT(*) AS total_shipping,
    9.45 * COUNT(*) AS total_tax
    INTO cart_count
    FROM carts c
    JOIN products p ON c.product_id = p.id;

  SELECT 
    SUM("total_price") AS total_price,
    SUM(quantity) AS total_quantity,
    COUNT(DISTINCT "user_id") AS unique_users_count,
    ROUND(CAST(SUM(quantity) AS FLOAT) / COUNT(DISTINCT "user_id")) AS avg_order_per_user
    INTO order_count
    FROM "orders";

sources:
  - name: products
    table_name: products
    connection: !Ref stic_conn
    columns:

  - name: carts
    table_name: carts
    connection: !Ref stic_conn
    columns:

  - name: users
    table_name: users
    connection: !Ref stic_conn
    columns:

  - name: orders
    table_name: orders
    connection: !Ref stic_conn
    columns:

endpoints:
  - name: products
    path: /products
    table_name: products

  - name: carts
    path: /carts
    table_name: carts

  - name: users
    path: /users
    table_name: users

  - name: orders
    path: /orders
    table_name: orders

  - name: cart_count
    path: /cart_count
    table_name: cart_count

  - name: order_count
    path: /order_count
    table_name: order_count