Solved

oracle sql

Posted on 2011-09-21
11
510 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:it-rex
  • 6
  • 3
  • 2
11 Comments
 
LVL 11

Author Comment

by:it-rex
ID: 36575311
so far
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
0
 
LVL 7

Accepted Solution

by:
Jacobfw earned 500 total points
ID: 36575350
Try this one:

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    substr(t1.timestamp,6,3) = 'sep'                                   and    active_indicator = 'yes')


0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36575381
>> 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(+)
...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36575393
>>Try this one:

Why hit the table three times?
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36575619
>>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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Author Comment

by:it-rex
ID: 36575719
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
0
 
LVL 11

Author Comment

by:it-rex
ID: 36575726
@slightwv:
>> between Sep 1st and sep 30th of any year

This requirement almost makes this sound like homework or an exam question.


why?
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36575741
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.
0
 
LVL 11

Author Comment

by:it-rex
ID: 36575746
yours works
and    substr(t1.timestamp,6,3) = 'sep'    
I have changed
to    and    TO_CHAR (t1.TIMESTAMP, 'mm') = '09'

and it works
0
 
LVL 11

Author Comment

by:it-rex
ID: 36575752
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')
0
 
LVL 11

Author Closing Comment

by:it-rex
ID: 36575754
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now