test 16721723, 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 PRIMARY KEY
  );
  
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')
;

SELECT DISTINCT erp.orders.userid, COUNT(erp.orders.userid)
FROM erp.orders
WHERE erp.orders.userid IN (
    SELECT erp.orders.userid
    FROM erp.orders, prod.referral_order_delivered
    WHERE erp.orders.userid = prod.referral_order_delivered.user_id
    AND erp.orders.paidat::date >= '2017-06-07'
    AND erp.orders.paidat::date <= '2017-07-07'
)
GROUP BY erp.orders.userid;


absolute service time: 0,44 sec