it-rex
asked on
oracle sql
attached is a data dump file for a schema called test
We need to Generate a list of unique email addresses with the latest name, gender
and age for a user with that email
The selection criteria limit the list to users which never subscribed to
anything; or;
users with inactive subscriptions; or;
users with active subscriptions that renewed between Sep 1st and sep
30th of any year
sql output should be like as far as i'm understanding:
a@a.com m 31 robert
b@b.com f 22 lulu
c@c.com f 08 kim
d@d.com m 22 Jay
e@e.com f 60 Will
TES.DMP
We need to Generate a list of unique email addresses with the latest name, gender
and age for a user with that email
The selection criteria limit the list to users which never subscribed to
anything; or;
users with inactive subscriptions; or;
users with active subscriptions that renewed between Sep 1st and sep
30th of any year
sql output should be like as far as i'm understanding:
a@a.com m 31 robert
b@b.com f 22 lulu
c@c.com f 08 kim
d@d.com m 22 Jay
e@e.com f 60 Will
TES.DMP
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> between Sep 1st and sep 30th of any year
This requirement almost makes this sound like homework or an exam question.
>>which never subscribed to anything
Look at outer joins.
I cannot import your file due to policy where I work but try this:
...
WHERE usr.user_id = sub.user_id(+)
...
This requirement almost makes this sound like homework or an exam question.
>>which never subscribed to anything
Look at outer joins.
I cannot import your file due to policy where I work but try this:
...
WHERE usr.user_id = sub.user_id(+)
...
>>Try this one:
Why hit the table three times?
Why hit the table three times?
>>Try this one:
Why hit the table three times?
Only to keep it simple and demonstrate the three criteria seperately. In addition, outer joins (+) and combinations of "and/or" / "exists/not exists" can be very tricky. There is some overhead and always to opportunity to rewrite the code for performance issues later.
Why hit the table three times?
Only to keep it simple and demonstrate the three criteria seperately. In addition, outer joins (+) and combinations of "and/or" / "exists/not exists" can be very tricky. There is some overhead and always to opportunity to rewrite the code for performance issues later.
ASKER
I'm still missing thses entries
a@a.com m 31 robert
d@d.com m 22 Jay
and I think I need them as per the BRS
a@a.com m 31 robert
d@d.com m 22 Jay
and I think I need them as per the BRS
ASKER
@slightwv:
>> between Sep 1st and sep 30th of any year
This requirement almost makes this sound like homework or an exam question.
why?
>> between Sep 1st and sep 30th of any year
This requirement almost makes this sound like homework or an exam question.
why?
My Query returns:
a@a.com m 31 robert
b@b.com f 22 lulu
c@c.com f 08 kim
d@d.com m 22 jay
e@e.com f 60 will
those indicated entries are included.
a@a.com m 31 robert
b@b.com f 22 lulu
c@c.com f 08 kim
d@d.com m 22 jay
e@e.com f 60 will
those indicated entries are included.
ASKER
yours works
and substr(t1.timestamp,6,3) = 'sep'
I have changed
to and TO_CHAR (t1.TIMESTAMP, 'mm') = '09'
and it works
and substr(t1.timestamp,6,3) = 'sep'
I have changed
to and TO_CHAR (t1.TIMESTAMP, 'mm') = '09'
and it works
ASKER
select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
from users u2
where u2.email = u1.email)
and not exists (select 1
from subscriptions s1
where s1.user_id = u1.user_id)
union
select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
from users u2
where u2.email = u1.email)
and exists (select 1
from subscriptions s1
where s1.user_id = u1.user_id
and active_indicator = 'No')
union
select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
from users u2
where u2.email = u1.email)
and exists (select 1
from subscriptions s1, transactions t1
where s1.user_id = u1.user_id
and t1.subscription_id = s1.subscription_id
and TO_CHAR (t1.TIMESTAMP, 'mm') = '09'
and active_indicator = 'yes')
from users u1
where u1.User_id = (select max (u2.user_id)
from users u2
where u2.email = u1.email)
and not exists (select 1
from subscriptions s1
where s1.user_id = u1.user_id)
union
select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
from users u2
where u2.email = u1.email)
and exists (select 1
from subscriptions s1
where s1.user_id = u1.user_id
and active_indicator = 'No')
union
select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
from users u2
where u2.email = u1.email)
and exists (select 1
from subscriptions s1, transactions t1
where s1.user_id = u1.user_id
and t1.subscription_id = s1.subscription_id
and TO_CHAR (t1.TIMESTAMP, 'mm') = '09'
and active_indicator = 'yes')
ASKER
thanks
ASKER
I have this code which is not returning the correct result
SELECT email,
gender,
age,
name
FROM test.users usr, TEST.SUBSCRIPTIONS sub, TEST.TRANSACTIONS trans
WHERE usr.user_id = sub.user_id
AND sub.SUBSCRIPTION_ID = trans.SUBSCRIPTION_ID
AND ( ( SUB.ACTIVE_INDICATOR = 'yes'
AND trans.action = 'renewal'
AND TO_CHAR (trans.TIMESTAMP, 'mm') = '09')
OR SUB.ACTIVE_INDICATOR = 'no'
)
as I am still missing the piece of
The selection criteria limit the list to users which never subscribed to
anything