372917932, PostgreSQL

Language: Layout:

--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     varchar(40)
    )
    ;
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
('1' ),
('2'),
('1' ),
('1' ),
('1' )
;
INSERT INTO erp.users VALUES
('1','Jean', 'Dupont','0623562783', '124'),
('2','Anne', 'Beaujeu', '0654738293', DEFAULT ),
('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
         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')

SELECT agg1.userid, agg1.countorders, agg2.countreferral
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

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


absolute service time: 0,4 sec