Demande complète, PostgreSQL

Demande complète

--PostgreSQL 9.6
--'\\' is a delimiter

CREATE SCHEMA erp;
CREATE TABLE erp.users(
   firstname    varchar(40)
   lastname
   phone
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 prod.referral_order_delivered.user_id,
       count(prod.referral_order_delivered.user_id)
FROM prod.referral_order_delivered
WHERE prod.referral_order_delivered.user_id IN
    (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' )
GROUP BY prod.referral_order_delivered.user_id


run \| edit \| history \| help	0