Please Help Tune a Query

[tekpr00]

Hello All, Can you please help tune my query. I have the following tables, data and expected result.

Table USERS
User_id (PK) (generated via a sequence)
Email
Gender
Age
Name

data
user_id email gender age name
1 a@a.com <mailto:a@a.com> m 30 rob
2 a@a.com <mailto:a@a.com> m 31 robert
3 b@b.com <mailto:b@b.com> f 18 lucie
4 b@b.com <mailto:b@b.com> f 22 lulu
5 c@c.com <mailto:c@c.com> m 10 kim
6 c@c.com <mailto:c@c.com> f 18 kim
7 c@c.com <mailto:c@c.com> f 08 kim
8 d@d.com <mailto:d@d.com> f 18 JJ
9 d@d.com <mailto:d@d.com> m 22 Jay
10 e@e.com <mailto:e@e.com> f 88 Bill
11 e@e.com <mailto:e@e.com> f 88 Will
12 e@e.com <mailto:e@e.com> f 60 Will
13 f@f.com <mailto:f@f.com> m 70 George

table subscriptions
columns:
SUbscription_id (PK) (generated via a sequence)
user_id (UK) (FK from users)
subscription_type (UK)
active_indicator

Data
subscription_id user_id subsciption_type active_indicator
1 2 Magazine Yes
2 3 Music CD No
3 3 Magazine Yes
4 3 Video Yes
5 8 Magazine Yes
6 9 Video Yes
7 10 Magazine No
8 13 Magazine yes

table TRANSACTIONS
columns:
subscription_id (PK) (FK from subscriptions)
action (PK)
timestamp (PK)

Data
subscription_id action timestamp
1 Renewal 2002-sep-10
2 Renewal 2002-Jan-01
2 Cancellation 2002-Feb-01
3 Renewal 2002-Aug-20
4 Renewal 2002-Aug-01
4 Renewal 2002-Sep-01
5 Renewal 2002-Aug-01
6 Renewal 2001-Sep-01
7 Renewal 2002-Sep-01
7 Cancellation 2002-Sep-10

The requirements and expected result are as follows:

Some assumptions will need to be made:
1)list any assumptions.
2) Generate a list of unique email addresses with the latest name, gender
and age for a user with that email
where clause
The selection criteria limit the list to users
a) which never subscribed to anything or
b) users with inactive subscriptions or
c) users with active subscriptions that renewed between Sep 1st and sep
30th of any year

Expected result:

a@a.com <mailto:a@a.com> m 31 robert
b@b.com <mailto:b@b.com> f 22 lulu
c@c.com <mailto:c@c.com> f 08 kim
d@d.com <mailto:d@d.com> m 22 Jay
e@e.com <mailto:e@e.com> f 60 Will

However when I prep the tables, and run this query:
my query[highlight #CC0000][/highlight]
select u.user_id, u.email, u.gender, u.age, u.name
from users u
inner join subscriptions s on u.user_id = s.user_id
inner join transactions t on s.subscription_id = t.subscription_id
where s.active_indicator like '%No%'
or t.action like '%Cancellation%'
or to_char(t.TIMEPSTAMP, 'mon-dd') between 'Sept-01' and 'Sept-30'

[highlight #CC0000]My result is:[/highlight]
USER_ID EMAIL GENDER AGE NAME
3 b@b <mailto:b@b.com> f 18 lucie
3 b@b <mailto:b@b.com> f 18 lucie
10 e@e <mailto:e@e.com> f 88 Bill
10 e@e <mailto:e@e.com> f 88 Bill
3 b@b <mailto:b@b.com> f 18 lucie

As you can see, I still have work to do on the DISTINCT query and such. Please help if you could and thanks very much.

 

[carp]

OK, for starters, notice that robert does not have any subscriptions. This should tell you that inner joins on subscriptions will not pick him up - but LEFT OUTER joins ought to do the trick:

select u.user_id, u.email, u.gender, u.age, u.name
from users u
         LEFT OUTER join subscriptions s on u.user_id = s.user_id
         LEFT OUTER join transactions t on s.subscription_id = t.subscription_id
where s.active_indicator like '%No%'
or t.action like '%Cancellation%'
to_char(t.TIMEPSTAMP, 'mon-dd') between 'Sept-01' and 'Sept-30'

Also, thank you for posting your table structures, data, and what you have tried so far. This makes it much easier for us to help you!