Final 1, PostgreSQL

Final 1

--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
  );
  
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' )

;

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;


run \| edit \| history \| help	0