I received business task to prepare orders/sales report:

• Business requirement is, to be able, to show how much we sale from WWW source, in terms of new clients
• Technical requirements are:
  1. To connect: contact_form table, with orders table, by a algorithm:
     • join by email address
     • deduplication on order_id (we want to present max one order/sale for best contact_form)
     • best contact_form will have:
       • creation date maximum 30 days before order date (it represent new client - first contact within 30 days before order)
       • source - only www
  2. Report should refresh automatically

1) First, I will create separate table, for the report results:

create table sales_report (
    order_id            NUMBER,
    order_tms           DATE,
    customer_id         NUMBER,
    contact_form_id     NUMBER,
    contact_date        DATE,
    full_name           VARCHAR2(100),
    email_address       VARCHAR2(100),
    source              VARCHAR2(100),
    CONSTRAINT sales_report_pk PRIMARY KEY (order_id)
);

2) Second, I want to update email address (in order table) into 4 customers (with biggest amount of orders), in order to connect orders table with contact_form table

ALTER TABLE orders ADD email_address VARCHAR2(100);
/
MERGE INTO orders
    USING (
        --retriving 4 customers (with biggest amount of orders)
        select e.*, f.email_address
        from (
            select d.*,
            ROWNUM as intConncetion
            from (
                select o.customer_id,
                min(o.order_tms) as min_order_tms, 
                max(o.order_tms) as max_order_tms, 
                EXTRACT(DAY FROM (round(max(o.order_tms) - min(o.order_tms)))) as diff_tms,
                c.order_amount
                from orders o
                --filtering 4 csutomers with biggest amount of orders;
                join (
                    select b.*
                    from (
                        select a.*,
                        ROW_NUMBER() OVER(ORDER BY a.order_amount DESC) AS rankOrder_amount
                        from (
                            select customer_id, count(*) as order_amount
                            from orders
                            group by customer_id
                            order by 2 desc
                        ) a
                    ) b
                    where b.rankOrder_amount <= 4
                ) c
                on o.customer_id = c.customer_id
                group by o.customer_id, c.order_amount
            ) d
        ) e
        join (
            select a.*, ROWNUM as intConncetion
            from (
                select distinct email_address 
                from contact_form
            ) a
        ) f
        on e.intConncetion = f.intConncetion
        order by order_amount desc
    ) email
    ON (orders.customer_id = email.customer_id)

  WHEN MATCHED THEN
    UPDATE SET orders.email_address = email.email_address;
/
COMMIT;

results =>

select * from orders where email_address is not null;

3) I'm creating procedure to insert new rows into destination table: sales_report

CREATE OR REPLACE PROCEDURE p_sales_report IS
BEGIN

    INSERT INTO sales_report (order_id, order_tms, customer_id, contact_form_id, contact_date, full_name, email_address, source)
    SELECT a.order_id, a.order_tms, a.customer_id, a.contact_form_id, a.contact_date, a.full_name, a.email_address, a.source
    from (
        select o.*, c.CONTACT_FORM_ID, c.CONTACT_DATE, c.FULL_NAME, c.SOURCE,

        --best contact_form will have:
        --1) contact date maximum 30 days before order date (it represent new client)
        --2) source - only www
        (CASE 
        WHEN ((to_date(to_char(o.order_tms,'yy/mm/dd'),'yy/mm/dd') - c.contact_date) between 0 and 31)
        THEN ROW_NUMBER() OVER(PARTITION BY o.order_id, (CASE WHEN ((to_date(to_char(o.order_tms,'yy/mm/dd'),'yy/mm/dd') - c.contact_date) between 0 and 31) THEN 1 ELSE 0 END)
        ORDER BY c.contact_date ASC)
        END) AS best_www_lead

        from orders o 

        join contact_form c
        on o.email_address = c.email_address
            and o.email_address is not null

        ORDER BY o.order_id, c.contact_date ASC
    ) a

    --only new rows
    left outer join sales_report sales
    on a.order_id = sales.order_id

    where a.best_www_lead = 1 --sales requirements
    and a.SOURCE = 'WWW'
    and sales.order_id is null; --only new rows

    COMMIT;

END;

4) run procedure - we inserted 16 rows, and count results =>

BEGIN 
   p_sales_report; 
END;
/
select count(*) from sales_report;

5) Below I will present some logic behind the scenes =>

query =>

select a.*
from (
    select o.order_id, o.order_tms, o.customer_id, c.*,

    --best contact_form will have:
    --1) contact date maximum 30 days before order date (it represent new client)
    --2) source - only www
    (CASE 
        WHEN ((to_date(to_char(o.order_tms,'yy/mm/dd'),'yy/mm/dd') - c.contact_date) between 0 and 31)
        THEN ROW_NUMBER() OVER(PARTITION BY o.order_id, (CASE WHEN ((to_date(to_char(o.order_tms,'yy/mm/dd'),'yy/mm/dd') - c.contact_date) between 0 and 31) THEN 1 ELSE 0 END)
        ORDER BY c.contact_date ASC)
    END) AS best_www_lead

    from orders o 

    join contact_form c
    on o.email_address = c.email_address
        and o.email_address is not null
        and o.order_id in (515, 1280)

    ORDER BY o.order_id, c.contact_date ASC
) a
where a.best_www_lead <= 3;

click to zoom in =>

6) Test results (if new sale appear?), after insert new row into orders table =>

INSERT INTO orders (order_tms, customer_id, order_status, store_id, email_address)
values ('21/09/01', 166, 'COMPLETE', 1, 'PRZEMEK@GOOGLE.PL');
/
COMMIT;
/
BEGIN 
   p_sales_report; 
END;
/
select count(*) from sales_report;

7) Great, everything works perfect. Now, optional you can make job - to repeat procedure execution everyday =>

BEGIN 
   dbms_scheduler.create_job ( 
    job_name => 'job_p_sales_report', 
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'p_sales_report',
    start_date => CURRENT_TIMESTAMP, 
    enabled => true, 
    repeat_interval => 'FREQ=DAILY;INTERVAL=1; byhour=11; byminute=26;'
   ); 
END;