I want to find users who have more than x pages views yesterday and
more than y page views during the last 30 days. My two queries work
well independently. But how do I find users that satisfy both
conditions?

SELECT DISTINCT user_id
FROM `pageviews`
WHERE pagevisit LIKE '2007-03-23%'
GROUP BY user_id
HAVING count( id ) > x

SELECT user_id
FROM pageviews
WHERE DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= pagevisit
GROUP BY user_id
HAVING count(DISTINCT(date(pagevisit))) > y

The UNION of both these queries also works fine, but that would give
me users that satisfy either condition, right? I only want users that
satisfy *both* conditions.

Any help would be greatly appreciated.

Cheers,
Krishna Srinivasan.

cookieplanter wrote:
> I want to find users who have more than x pages views yesterday and
> more than y page views during the last 30 days. My two queries work
> well independently. But how do I find users that satisfy both
> conditions?
>
> SELECT DISTINCT user_id
> FROM `pageviews`
> WHERE pagevisit LIKE '2007-03-23%'
> GROUP BY user_id
> HAVING count( id ) > x
>
> SELECT user_id
> FROM pageviews
> WHERE DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= pagevisit
> GROUP BY user_id
> HAVING count(DISTINCT(date(pagevisit))) > y
>
> The UNION of both these queries also works fine, but that would give
> me users that satisfy either condition, right? I only want users that
> satisfy *both* conditions.
>
> Any help would be greatly appreciated.
>
> Cheers,
> Krishna Srinivasan.

Well you could really do with INTERSECT, but of course MySQL doen't support
it yet.

So you could make one of them a subquery and do a FULL OUTER JOIN, rejecting
all records where either side of the join is null.

Paul,

Untested, but try this:

SELECT pv1.userid FROM
(
SELECT DISTINCT user_id
FROM `pageviews`
WHERE pagevisit LIKE '2007-03-23%'
GROUP BY user_id
HAVING count( id ) > x
) pv1
INNER JOIN
(
SELECT user_id
FROM pageviews
WHERE DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= pagevisit
GROUP BY user_id
HAVING count(DISTINCT(date(pagevisit))) > y
) pv2
on pv1.user_id = pv2.user_id

-- Bill

"Paul Lautman" <paul.lautman@btinternet.com> wrote in message
news:56ilceF26qukeU1@mid.individual.net...
> cookieplanter wrote:
>> I want to find users who have more than x pages views yesterday and
>> more than y page views during the last 30 days. My two queries work
>> well independently. But how do I find users that satisfy both
>> conditions?
>>
>> SELECT DISTINCT user_id
>> FROM `pageviews`
>> WHERE pagevisit LIKE '2007-03-23%'
>> GROUP BY user_id
>> HAVING count( id ) > x
>>
>> SELECT user_id
>> FROM pageviews
>> WHERE DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= pagevisit
>> GROUP BY user_id
>> HAVING count(DISTINCT(date(pagevisit))) > y
>>
>> The UNION of both these queries also works fine, but that would give
>> me users that satisfy either condition, right? I only want users that
>> satisfy *both* conditions.
>>
>> Any help would be greatly appreciated.
>>
>> Cheers,
>> Krishna Srinivasan.
>
> Well you could really do with INTERSECT, but of course MySQL doen't
> support it yet.
>
> So you could make one of them a subquery and do a FULL OUTER JOIN,
> rejecting all records where either side of the join is null.
>

On Mar 24, 1:36 am, "AlterEgo" <altereg...@dslextreme.com> wrote:
> Paul,
>
> Untested, but try this:
>
> SELECT pv1.userid FROM
> (
> SELECT DISTINCT user_id
> FROM `pageviews`
> WHERE pagevisit LIKE '2007-03-23%'
> GROUP BY user_id
> HAVING count( id ) > x
> ) pv1
> INNER JOIN
> (
> SELECT user_id
> FROM pageviews
> WHERE DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= pagevisit
> GROUP BY user_id
> HAVING count(DISTINCT(date(pagevisit))) > y
> ) pv2
> on pv1.user_id = pv2.user_id
>
> -- Bill
>
> "Paul Lautman" <paul.laut...@btinternet.com> wrote in message
>
> news:56ilceF26qukeU1@mid.individual.net...
>
> > cookieplanter wrote:
> >> I want to find users who have more than x pages views yesterday and
> >> more than y page views during the last 30 days. My two queries work
> >> well independently. But how do I find users that satisfy both
> >> conditions?
>
> >> SELECT DISTINCT user_id
> >> FROM `pageviews`
> >> WHERE pagevisit LIKE '2007-03-23%'
> >> GROUP BY user_id
> >> HAVING count( id ) > x
>
> >> SELECT user_id
> >> FROM pageviews
> >> WHERE DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= pagevisit
> >> GROUP BY user_id
> >> HAVING count(DISTINCT(date(pagevisit))) > y
>
> >> The UNION of both these queries also works fine, but that would give
> >> me users that satisfy either condition, right? I only want users that
> >> satisfy *both* conditions.
>
> >> Any help would be greatly appreciated.
>
> >> Cheers,
> >> Krishna Srinivasan.
>
> > Well you could really do with INTERSECT, but of course MySQL doen't
> > support it yet.
>
> > So you could make one of them a subquery and do a FULL OUTER JOIN,
> > rejecting all records where either side of the join is null.

This works! Thank you so much.

Krishna Srinivasan.