CODEWITHSUNDEEP
mysql
danjfoley
danjfoley

Reputation: 808

Select users who's first appearance has a referrer value of adwords and appear more then once

I have a table of users, one for each order. Every order comes with a referrer. I want to select those users who's first appearance in the table (by order_id) has a referrer of 'adwords' and have more then one entry in the table.

The columns here to use are: order_id - every new order gets a new order_id that is one more then the last one billing_email - the users email, which is how we know what user they are referrer - this is set to adwords when they come in via adwords.

So far i have this:

select count(*) as num_entries, billing_email from tblOrder where referrer='adwords' GROUP BY billing_email HAVING count(*) > 1

That's not doing what i'm looking for.

What i'm like to see a results is something like this:

2, [email protected]
4, [email protected]

etc. thus telling me how many repeat orders people coming in from adwords make.

Upvotes: 0

Views: 39

Answers (1)

Andrew G
Andrew G

Reputation: 349

Your query needs to be a little bit more complicated to work exactly as you want.

First you need an inner query that will allow you to get the minimum order_id for each billing_email. It also lets you count the total entries for each billing_email so you can only include users with more than one.

Then you have to use that minimum order_id to join back into the same table and get the rest of the data from that row. This will allow you to see if each user's minimum order_id was referred from adwords.

SELECT T1.TableEntries, T1.billing_email FROM
(
    SELECT billing_email, MIN(order_id) AS UsersFirstOrderID
    , COUNT(*) AS TableEntries
    FROM tblOrder 
    GROUP BY billing_email
    HAVING TableEntries>1
) AS T1
JOIN
tblOrder AS T2
ON T1.UsersFirstOrderID=T2.order_id
AND T2.referrer='adwords'

Upvotes: 1

Related Questions