How can i write this sql statement in domo?

vikrao

where a.Customer NOT IN(select distinct Customer from CustomerSalesData where InvoiceDate < '2020-07-01')

Statement below is a column I use for an sql query..

ROW_NUMBER() over(partition by a.Customer order by MIN(a.InvoiceDate)) as OrderNum

I am getting an error while trying to convert this statement. I am not sure how to convert it into something that Domo will understand.

Can this be done in beast mode or MagicETL?

Thanks for any help.

MarkSnodgrass

For you first statement, you can split out your data and perform a number of functions with tiles and then join it back together. Something like this:

Your filter tile would filter to the invoice date that you want. The Group By tile would give you distinct list by just choosing customer as your column and then do some sort of aggregation, such as count. Then join it back to your original dataset and choose left join with your original dataset being on the left. After the join, use another filter tile to filter where a column from the right table is null. This would give you your customers not in your subquery.

Your second one, you can use the Rank and Window tile to do the row number. I would suggest looking at this kb article if you haven't looked at it yet.

https://domohelp.domo.com/hc/en-us/articles/360044876094-New-Magic-ETL-Tiles-Aggregate#3.

vikrao

Thanks Mark.

I was able to use the Rank and File tile to get some of the data that I needed. I will try splitting and joining the data in MagicETL.

vikrao

What if my invoice date keeps changing? I collect data for this by month so I keep changing the invoice date that I look back at.

jaeW_at_Onyx

@vikrao what's the business question you're trying to answer in plain english?

i suspect it's something like "which users havent paid an invoice in the last x months.

if so rework your filter as a formula based off CURRENT_DATE

vikrao

sorry for late reply. I am trying to find 'first orders from customers who have requested samples from us in the past'

vikrao

To explain more precisely,

If I have 100 sample requests in January, I want to find out how many of those customers who requested samples converted to first orders in the next 15 days, 30 days, 45 days.

jaeW_at_Onyx

uh...

would it be fair to say you want to rank orders by date ascending, and just want to know the date_diff of the first order and today?

do your rank() function in ETL and then do your datediff in Beast Mode.

vikrao

Yes, that would be fair to say. I will try it out and update this thread if I can get it to work.