it could be something like this,
but if I wanted nothing, not like frisbee.
So, do you think this is going to work?
No. Because you forgot your wildcard operators.
Any amount of characters that come before,
any amount of characters that come after 633 products,
how many do you think we'll get now?
Do you think we'll get 631 or do you think we'll
get where I need to have what we're actually filtering on?
Where the product name is not like frisbee?
So, what do you think 633, less, more,
greater or equal to?
So, this is the first insight that it is case sensitive.
So, I have a lowercase f here because I like tricking you guys.
So, one of the things that you can do is wrap this in a lower function,
which basically means passing all the product names,
but first convert all the characters to lowercase and then match on this frisbee.
So, now will see how products this excludes,
making sure that frisbee is no longer there.
So, 631 this is two dog frisbees are out. So, that's great.
We have confirmed that this works and now let's see what ultimately we want to answer.
We need to get a bunch of information on products.
So, first I'm going to bring
our assumption of every row being a view on a particular product,
and in the sessions there should be a field,
as we've been working with so far for the quantity that was ordered.
As you can see product quantity right there,
so let's add in the product quantity,
which indicates to us whether it was part of an incomplete.
When I say an incomplete order that means it was added to the cart,
but the person did not checkout yet.
We'll call it an abandoned cart or it's part of an order that
has actually gone through completion and has revenue associated with it.
So, we're going to count,
and again we're not doing the sum here.
We are going to count that as
potential orders or order complete or incomplete orders whenever you want to call it,
and that's just going to give us again,
if this is a 10 right here,
that's going to count once.
Let's see, we also want to include how many were ordered,
how many non-frisbees were ordered,
and that's going to be the quantity of products that were added.
Now let's see, we have the product name associated with it if we're doing aggregation,
we need to make sure we aggregate on the non aggregating fields.
All right. Let's see if we can't run this and see if it gets us a little bit closer,
we also want to adjust the top ten,
so let's just put our limit there now.
So, the results panel doesn't get too cluttered.
One selected on that, it'll give us 10 records that aren't frisbees,
we're getting a little bit closer now.
So you've the views, we have the time it was added to
an order and we've got a product the amount that was added,
but we have some lower numbers here,
and so if you remember the first part of the query was.
for products with over a thousand units that have been out of the currency.
Units means we need to filter by the quantity product added.
Now, I can't do this in the where clause because
it's an aggregation and I can't use aliases in the where clause.
So, if you're filtering on an aggregation,
immediately you want to think about using the lesser-known SQL clause of having,
and you can use aliases here since that's executed after the initial filtering happens.
Where quantity product added,
I think we want greater than a thousand.
So let's see if that changes our results.
So, all the rest of the ones should drop off except for
maybe that foam can and bottle cooler we will see. This is the minimum.
Again, there's no orderings,
it's kind of choosing a random result here.
So, we want to order by the ones have the highest conversion rate first.
So, conversion rate here is going to be defined as
the amount of views that the product has,