Hello @markmegerian
Can you guide me on this, I want to filter out the customers who haven’t transacted in the past X days (below 90 days) I have a customer vertex which is directly connected to orders and a calendar vertex which is directly connected to orders
I have a strategy to do this, But I think this one is not effective.
1st step. get all the orders of all past 90 days and get their corresponding Customer vertex
2nd step. then get all customers only
FinalStep. 2ndStep (All Customers) MINUS 1stStep (Customers)
How can we apply outdegree here?
Calendar having id i.e contains unique datetime string, which I am using mainly for to get the orders on a Particular date or in between dates
I think the approach with using the MINUS operator is the best way to do this.
Please show a code example and we can look at it more closely. The reason I like the MINUS technique is that its very fast to get all customers, and it should be quite fast to get customers with orders in the last 90 days, and then using the MINUS operator is very efficient. This is something in graph db that shows how much more powerful in this example than relational db.
Also PLEASE put the GSQL tags on your posts, otherwise they will not appear in the right place in the forum.
customer_having_orders = select cst from start:ord-(customer_orders:cord)-customer:cst where ord.transaction_datetime > datetime_sub(to_date, INTERVAL str_to_int(from_days) DAY) and ord.transaction_datetime < to_date
customer_dont_orders_in_range = customer_having_orders MINUS all_customers
Final exclude of Who joins in between who_joins_in_btw = select s from customer_dont_orders_in_range:s where s.created_at > datetime_sub(to_date, INTERVAL str_to_int(from_days) DAY) and s.created_at < to_date
result_red = customer_dont_orders_in_range MINUS who_joins_in_btw
This one is going to MINUS from (miliions of all_customers) that is my concern, or this logic is the perfect one taking advantage of Graph DB?
When you create a new topic, rather than just putting in a person’s name to reply, if you specify the category of Tigergraph Platform | GSQL category, then everyone in the forum will see it. Right now you only see it if you look at Uncategorized topics, and I don’t think most people do that.
For an existing entry, click on the little “pencil” icon to edit your topic, and apply the GSQL category, so that people will see them when they look for the GSQL category of topics.
As to your questions: in graph, there is no reason to avoid instantiating a large vertex set, so I wouldnt worry about that. However there are several changes needed
if we pass from_days = 90
start = {orders.*};
all_customers = {customer.*};
DATETIME to_date = now();
# you dont need the str_to_int, the from_days should already be an INT
customer_having_orders = select cst from start:ord-(customer_orders:cord)-customer:cst where ord.transaction_datetime > datetime_sub(to_date, INTERVAL from_days DAY) and ord.transaction_datetime < to_date;
# you had this backwards
customer_dont_orders_in_range = all_customers MINUS customer_having_orders
# Final exclude of Who joins in between can be done in one step
result_red = select s from customer_dont_orders_in_range:s
where s.created_at <= datetime_sub(to_date, INTERVAL from_days DAY) ;