public
Created

  • Download Gist
dbext.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163
-- 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')
;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.