dbext.sql

-- dbext:type=PGSQL:user=yangchenyun:dbname=huali_development:host=localhost:port=5432

select source, count(id)
from orders
group by source
;

select name_zh, sum(quantity)
from products, line_items
where products.id = line_items.product_id
group by name_zh
;

select 
--  order information
orders.id, orders.delivery_date, 
-- shipment information
addresses.fullname,
addresses.phone,
areas.name,
addresses.address,
orders.special_instructions
from orders, shipments, ship_methods, addresses, areas
where orders.id = shipments.order_id
and shipments.ship_method_id = ship_methods.id
and ship_methods.id = 3
and orders.state = 'wait_ship'
and orders.delivery_date = '2013-02-14'
and addresses.id = orders.address_id
and areas.id = addresses.area_id
order by areas.name
;

--  Operation Data
-- Monthly Aggregation
select date_part('month', orders.created_at) as Month, 
  count(distinct orders.id) as OrderNumber, 
  sum(payment_total) as Revenue
from orders
where (state != 'void' and state != 'generated')
GROUP BY date_part('month', orders.created_at)
;

select count(id)
from orders
where orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01' 
and (state != 'void' and state != 'generated')
;


-- Daily Details
select date_part('day', orders.created_at) as day,
payment_total as paid_amount,
orders.created_at as purchase_time,
products.name_zh
from orders, line_items, products
where orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and orders.id = line_items.order_id
and line_items.product_id = products.id
and (state != 'void' and state != 'generated')
order by day
;

-- Daily Distribution
select date_part('day', orders.created_at) as day, count(id) as orderNum
from orders
where orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01' 
and (state != 'void' and state != 'generated')
group by date_part('day', orders.created_at)
order by day
;


-- Order Analysis
-- by Area
select date_part('month', orders.created_at) as Month, 
  provinces.name, 
  count(orders.id), 
  sum(payment_total) as Revenue, 
  round(sum(payment_total)/ count(orders.id), 2) as RevenuePerOrder
from orders, addresses, provinces
where orders.address_id = addresses.id
and orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and provinces.id = addresses.province_id
and (state != 'void' and state != 'generated')
group by provinces.name, date_part('month', orders.created_at)
order by month, count(orders.identifier) desc
;

-- by Source
select date_part('month', orders.created_at) as Month, 
  source,
  count(orders.id), 
  sum(payment_total) as Revenue, 
  round(sum(payment_total)/ count(orders.id), 2) as RevenuePerOrder
from orders
where (state != 'void' and state != 'generated')
and orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
group by orders.source, date_part('month', orders.created_at)
order by month, count(orders.identifier) desc
;

-- by Product
select date_part('month', orders.created_at) as Month, 
  products.name_zh,
  count(orders.id), 
  --  payment_total is calculated multiple times for orders which have multiple products
  sum(payment_total) as Revenue
from orders, line_items, products
where orders.id = line_items.order_id
and orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and line_items.product_id = products.id
and (state != 'void' and state != 'generated')
group by products.id, date_part('month', orders.created_at)
order by month, count(orders.identifier) desc
;

select *
from line_items as l1, line_items as l2
where l1.order_id = l2.order_id
and l1.id <> l2.id
;

-- by Delivery
select date_part('month', orders.created_at) as Month, 
  ship_methods.name,
  count(orders.id) as OrderNum,
  sum(payment_total) as Revenue, 
  round(sum(payment_total)/ count(orders.id), 2) as RevenuePerOrder
from orders, shipments, ship_methods
where orders.id = shipments.order_id
and orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and shipments.ship_method_id = ship_methods.id
and (orders.state != 'void' and orders.state != 'generated')
group by ship_methods.name, date_part('month', orders.created_at)
order by month, count(orders.id) desc
;

select date_part('month', orders.created_at) as Month, 
  ship_methods.name,
  count(orders.id) as OrderNum,
  sum(payment_total) as Revenue, 
  round(sum(payment_total)/ count(orders.id), 2) as RevenuePerOrder
from orders, shipments, ship_methods
where orders.id = shipments.order_id
and orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and (orders.state != 'void' and orders.state != 'generated')
and shipments.ship_method_id = ship_methods.id
group by ship_methods.name, date_part('month', orders.created_at)
order by month, count(orders.id) desc
;


select * from orders
where orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and (orders.state != 'void' and orders.state != 'generated')
EXCEPT 
select orders.*
from orders, shipments
where orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and orders.id = shipments.order_id
and (orders.state != 'void' and orders.state != 'generated')
;