I am trying to create a view which contain Suspicious order from a orders table.
The condition for the suspicious order is, every new order(in an interval), which have "New Customer" tag and used the discount codes(_sdc_sequence) from a table orders__discount_codes and either zip code or phone number of customer is matching in previous to that interval.
My attempt is
- Created a view which contain all old orders(previous to the interval of 2 days) with "new customer tag"
CREATE OR REPLACE VIEW schema.old_orders_view AS
SELECT odr.id, odr.customer__id,odr.name, odr.billing_address__phone, odr.shipping_address__zip,odr.order_number, odr.updated_at
FROM schema.orders odr, schema.orders__discount_codes odc
WHERE odr._sdc_sequence=odc._sdc_sequence
AND
odr.updated_at<now() - interval '2 day'
AND
odr.tags LIKE'%New Customer%'
AND
odr.cancelled_at is null
AND
odr.confirmed ='t';
- Created a view containing new orders(within the period 2 days)
CREATE OR REPLACE VIEW schema.new_orders_view AS
SELECT odr.id, odr.customer__id,odr.name, odr.billing_address__phone, odr.shipping_address__zip,odr.order_number, odr.updated_at
FROM schema.orders odr, schema.orders__discount_codes odc
WHERE odr._sdc_sequence=odc._sdc_sequence
AND
odr.updated_at>=now() - interval '2 day'
AND
odr.tags LIKE'%New Customer%'
AND
odr.cancelled_at is null
AND
odr. confirmed ='t';
- later inner joined them
CREATE OR REPLACE VIEW schema.suspicious_orders_view AS
SELECT n_odr.customer__id new_customer__id,n_odr.name new_name,o_odr.customer__id old_customer__id,o_odr.name old_name,o_odr.updated_at old_updated_at,n_odr.updated_at new_updated_at, o_odr.id old_id, n_odr.id new_id
FROM
schema.new_orders_view n_odr, schema.old_orders_view o_odr
WHERE
o_odr.billing_address__phone=n_odr.billing_address__phone
OR
o_odr.shipping_address__zip=n_odr.shipping_address__zip;
What I need is the third View( suspicious_orders_view
).
Is there any way to optimize these queries? The table contain more than one 100K records. every day there is 50- 100 new records in new_orders_view
If the query is without two dummy view is more good (If it is not possible to optimize doing this also great).
I have used this in my application and tring to connect with google data studio
got error
ERROR:
Unable to Connect Host: An I/O error occurred while sending to the backed.
So, optimizing the query will be more appropriate.
I am using Postgresql 10.
Any help would be appreciated. Thank you in advance.
