CODEWITHSUNDEEP
mysqlsql
Tim
Tim

Reputation: 14446

MySQL - Finding a user's first referrer

I have a creative query request, with a few examples of my own.

I have a table that logs user's hits with the following fields:

(Note that I realize that using "unique" as a field name ended up being a terrible design choice, but putting it in backticks has helped avoid any issues...)

I would like a query which returns a list of uniques and their first referrer.

Upvotes: 1

Views: 301

Answers (2)

Umbrella
Umbrella

Reputation: 4788

If you don't have a hit_id field, you'll have to use the pair of (unique, date) as a row identifier. You should be able to get what you are looking for with something like this.

SELECT `referrer` FROM `hits` h1 INNER JOIN 
    (SELECT `unique`, MIN(`date`) FROM `hits` GROUP BY `unique`) h2
ON h1.`unique` = h2.`unique` AND h1.`date` = h2.`date`
GROUP BY `referrer`

If you have a primary key you didn't mention, like hit_id, it gets a bit shorter and saves you from the rare case that two hits occur from the same user in the same second:

SELECT `referrer` FROM `hits` h1 INNER JOIN 
    (SELECT MIN(`hit_id`) FROM `hits` GROUP BY `unique`) h2
ON h1.`hit_id` = h2.`hit_id`
GROUP BY `referrer`

In both cases, the last GROUP BY is just to remove dups in your final result set.

Upvotes: 1

Pierre de LESPINAY
Pierre de LESPINAY

Reputation: 46208

If you are looking the first referrer by date for each user you can do something like that:

CREATE TEMPORARY TABLE tmp_hits
SELECT
  `unique`
, `date`
, `referrer`
FROM log_table
ORDER BY `date` ASC
;

SELECT
  `unique`
, `referrer`
FROM tmp_hits
GROUP BY `unique`
;

Upvotes: 1

Related Questions