• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

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
0
it-rex
Asked:
it-rex
  • 6
  • 3
  • 2
1 Solution
 
it-rexAuthor Commented:
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
 
JacobfwCommented:
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
 
slightwv (䄆 Netminder) Commented:
>> 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
slightwv (䄆 Netminder) Commented:
>>Try this one:

Why hit the table three times?
0
 
JacobfwCommented:
>>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
 
it-rexAuthor Commented:
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
 
it-rexAuthor Commented:
@slightwv:
>> between Sep 1st and sep 30th of any year

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


why?
0
 
JacobfwCommented:
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
 
it-rexAuthor Commented:
yours works
and    substr(t1.timestamp,6,3) = 'sep'    
I have changed
to    and    TO_CHAR (t1.TIMESTAMP, 'mm') = '09'

and it works
0
 
it-rexAuthor Commented:
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
 
it-rexAuthor Commented:
thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now