Finale8E9237394, PostgreSQL

Finale8E9237394

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

CREATE SCHEMA erp;
CREATE TABLE erp.orders(
  "userId"    uuid,
  "paidAt"    timestamp DEFAULT current_timestamp
  );
  
CREATE SCHEMA prod;
CREATE TABLE prod.referral_order_delivered(
  user_id    text
  );
CREATE TABLE erp.users(  
    id            uuid,                   
    firstname     varchar(40),
    lastname      varchar(40),
    phone         varchar(40)   ,        
    "companyId"     integer
    )
    ;
CREATE TABLE erp.companies(
    id        integer,
    name      varchar(40),
    zoneId    integer
    )
    ;
CREATE TABLE prod.referral_code_used(
    godfather_id    text,
    id              integer,
    timestamp       timestamp
    
    );
INSERT INTO prod.referral_code_used (godfather_id, id, timestamp)
VALUES
    ('dfcfa623-0f68-492a-9a05-24c41de81674', '323232', '2017-04-04')
;

INSERT INTO erp.orders VALUES
    ('b0b87982-b551-4be7-98ee-8e7bb7ea0788','2017-01-01' ),
    ('5ae688e3-662f-4768-bd42-f4ff4179474d','2017-01-01' ),
    ('dfcfa623-0f68-492a-9a05-24c41de81674','2017-06-30' ),
    ('dfcfa623-0f68-492a-9a05-24c41de81674','2017-04-01' ),
    ('5ae688e3-662f-4768-bd42-f4ff4179474d','2017-06-07' ),
    ('8a5f052b-2125-46bc-ae00-f40610221283','2017-06-07' ),
    ('5ae688e3-662f-4768-bd42-f4ff4179474d','2017-01-01' ),
    ('5ae688e3-662f-4768-bd42-f4ff4179474d','2017-01-01' )
 
        ;
INSERT INTO prod.referral_order_delivered VALUES

('5ae688e3-662f-4768-bd42-f4ff4179474d'),
('dfcfa623-0f68-492a-9a05-24c41de81674' ),
('dfcfa623-0f68-492a-9a05-24c41de81674' ),
('dfcfa623-0f68-492a-9a05-24c41de81674' ),
('dfcfa623-0f68-492a-9a05-24c41de81674' )
;
INSERT INTO erp.users VALUES

('5ae688e3-662f-4768-bd42-f4ff4179474d','Anne', 'Beaujeu', '0654738293', DEFAULT ),
('dfcfa623-0f68-492a-9a05-24c41de81674','Jean', 'Dupont','0623562783', '124'),
('8a5f052b-2125-46bc-ae00-f40610221283','Alice', 'Terramine', '0673839203', DEFAULT ),
('b0b87982-b551-4be7-98ee-8e7bb7ea0788','Hugo', 'Blanchet', '06784859403', '218' )
;

INSERT INTO erp.companies VALUES
('124','Alchatech', '2'),
('218','SigmaX2', '4')
;
select i.order_month, avg(i.codeused)
from (
select u.id, lpad(extract(month from o.paid_at)::text, 2, '0') order_month, sum(case when rcu.id is not null then 1 else 0 end) codeused
    from erp.users u
    inner join (
        select o."userId", min(o."paidAt") paid_at
        from erp.orders o
        where
            o."paidAt"::date >= '2017-01-01'
        group by o."userId"
    ) o on o."userId" = u.id
    inner join prod.referral_code_used rcu on rcu.godfather_id::uuid = u.id
    where
        rcu.timestamp <= paid_at::timestamp - interval '1 month'
    group by u.id, lpad(extract(month from o.paid_at)::text, 2, '0')
    )i
group by i.order_month
order by i.order_month asc


run \| edit \| history \| help	0