finale47394372, PostgreSQL

Language: Layout:

--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


absolute service time: 0,5 sec