Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
FInale 38474297439
--PostgreSQL 9.6 --'\\' is a delimiter CREATE SCHEMA erp; CREATE TABLE erp.orders( id integer PRIMARY KEY, 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 ('374893429','1','2017-06-30' ), ('23718932','1','2017-06-18' ), ('23892031','2','2017-06-07' ), ('283238321','4','2017-06-07' ), ('8323218','3','2017-01-01' ), ('2819','2','2017-01-01' ), ('2383','2','2017-01-01' ), ('21839','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; -- 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
Q3
Ecommerce Website Analysis by SQL
Professional Networking Social Media Website SQL Analysis
Assignment 1(SET A)
loggin_trigger-audit
postgresql example hierarchy with addresses
Select the latest id from last week from each different contact without repeating barcodes.....
Movie Rating SQL Analysis Project
Pivot the Hard Way.
1075-1077 Project Analysis