Run Code
|
Code Wall
|
Users
|
Misc
|
Feedback
|
About
|
Login
|
Theme
|
Privacy
finale47394372
--PostgreSQL 9.6 --'\\' is a delimiter CREATE SCHEMA erp; CREATE TABLE erp.orders( userId integer, paidAt timestamp DEFAULT current_timestamp ); CREATE SCHEMA prod; CREATE TABLE prod.referral_order_delivered( user_id integer ); CREATE TABLE erp.users( id integer , firstname varchar(40), lastname varchar(40), phone varchar(40) , companyid integer ) ; CREATE TABLE erp.companies( id integer, name varchar(40), zoneid integer ) ; INSERT INTO erp.orders VALUES ('3','2017-01-01' ), ('2','2017-01-01' ), ('1','2017-06-30' ), ('1','2017-06-18' ), ('2','2017-06-07' ), ('4','2017-06-07' ), ('2','2017-01-01' ), ('2','2017-01-01' ) ; INSERT INTO prod.referral_order_delivered VALUES ('2'), ('1' ), ('1' ), ('1' ), ('1' ) ; INSERT INTO erp.users VALUES ('2','Anne', 'Beaujeu', '0654738293', DEFAULT ), ('1','Jean', 'Dupont','0623562783', '124'), ('3','Alice', 'Terramine', '0673839203', DEFAULT ), ('4','Hugo', 'Blanchet', '06784859403', '218' ) ; INSERT INTO erp.companies VALUES ('124','Alchatech', '2'), ('218','SigmaX2', '4') ; select * from erp.users; select * from erp.companies; select * from erp.orders; select * from prod.referral_order_delivered; select distinct erp.orders.paidat, erp.orders.userid from erp.orders; -- DERIVED TABLE EXAMPLE SELECT agg1.userid, agg1.countorders, agg2.countreferral FROM (SELECT j.userid, COUNT(j.userid) as countorders FROM (SELECT DISTINCT erp.orders.userid FROM erp.orders INNER JOIN prod.referral_order_delivered ON erp.orders.userid = prod.referral_order_delivered.user_id WHERE erp.orders.paidat >= '2017-06-07' AND erp.orders.paidat <= '2017-07-07') j INNER JOIN erp.orders e ON j.userid = e.userid GROUP BY j.userid) agg1 INNER JOIN (SELECT j.userid, COUNT(j.userid) as countreferral FROM (SELECT DISTINCT erp.orders.userid FROM erp.orders INNER JOIN prod.referral_order_delivered ON erp.orders.userid = prod.referral_order_delivered.user_id WHERE erp.orders.paidat >= '2017-06-07' AND erp.orders.paidat <= '2017-07-07') j INNER JOIN prod.referral_order_delivered p ON j.userid = p.user_id GROUP BY j.userid) agg2 ON agg1.userid = agg2.userid; -- CTE EXAMPLE: WITH j AS (SELECT DISTINCT erp.orders.userid FROM erp.orders WHERE erp.orders.paidat >= '2017-06-07' AND erp.orders.paidat <= '2017-07-07') SELECT erp.users.firstname, erp.users.lastname, erp.users.phone, agg1.countorders, agg2.countreferral, erp.companies.name, erp.companies.zoneid FROM (SELECT j.userid, COUNT(j.userid) as countorders FROM j INNER JOIN erp.orders e ON j.userid = e.userid GROUP BY j.userid) agg1 LEFT JOIN (SELECT j.userid, COUNT(j.userid) as countreferral FROM j INNER JOIN prod.referral_order_delivered p ON j.userid = p.user_id GROUP BY j.userid) agg2 ON agg1.userid = agg2.userid INNER JOIN erp.users ON agg1.userid=erp.users.id LEFT JOIN erp.companies ON erp.users.companyid=erp.companies.id order by countorders, countreferral
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
Projeto
testo
filme
mandelbrot set
conditionally add “another row” in SQL Server?
Movie Rating SQL Analysis Conducted by Miranda Zhao
postgresql: working days
PC02-DB-UTEC
Distinct row aggregation with ordering - stackoverflow.com/q/43249053/4116017
Return IDs where there are exactly 3 occurrences1
Please log in to post a comment.