Home » SQL & PL/SQL » SQL & PL/SQL » Trick question (oracle 9i)
Trick question [message #343553] Wed, 27 August 2008 09:11 Go to next message
alok416
Messages: 7
Registered: June 2005
Location: India
Junior Member


Dear All,

I've got one trick question, whhich I tried to solve, but really give up. Please go through the following.

Table USERS

columns:
User_id (PK)
Email
Gender
Age
Name

table SUBSCRIPTIONS
columns:
SUbscription_id (PK)
user_id (UK) (FK from users)
subscription_type (UK)
active_indicator

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


sample data:

USERS

user_id email   gender age   name
1      a@a.com   m     30    rob
2      a@a.com   m     31    robert
3      b@b.com   f     18    lucie
4      b@b.com   f     22    lulu

subscriptions

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

transactions
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


Type f business:

We are a subscription company where people signup to received products
like CDs, books, magazines, etc. We always try to market new
subscriptions to our existing client base, but having millions of
customers of which many use the same email address we like to send are
promotional email's once.

QUERY:

Some assumptions will need to be made. Please list any such assumptions.

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

answer should be:

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


hare krishna
Alok

Re: Trick question [message #343563 is a reply to message #343553] Wed, 27 August 2008 09:23 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What have you tried? post your code.
Re: Trick question [message #343627 is a reply to message #343553] Wed, 27 August 2008 12:08 Go to previous messageGo to next message
alok416
Messages: 7
Registered: June 2005
Location: India
Junior Member



I've tried the following.

[pre]

scott@KRISH> select email , gender , age , name , count(*) from users group by email , gender , age , name having count(*) < 2;

EMAIL GENDE AGE NAME COUNT(*)
---------- ----- ---------- ------------------------------ ----------
a@a.com m 30 rob 1
a@a.com m 31 robert 1
b@b.com f 18 lucie 1
b@b.com f 22 lulu 1
c@c.com f 8 kim 1
c@c.com f 18 kim 1
c@c.com m 10 kim 1
d@d.com f 18 jj 1
d@d.com m 22 jay 1
e@e.com f 60 will 1
e@e.com f 88 bill 1
e@e.com f 88 will 1
f@f.com m 70 george 1

[pre]

I know, this not gone help, becuse the result set does not contain duplicate values. I've really no clue how could I produce the desired result set.

hare krishna
Alok
Re: Trick question [message #343635 is a reply to message #343627] Wed, 27 August 2008 12:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
latest name, gender
and age for a user with that email


What would be the column that would define the latest name, gender and age.

Oh, and when you reply to this, also include a proper test case i.e. post create table and insert scripts. e.g.

create table mytab (col1 number, col2 varchar2(40));
insert into mytab values (10, 'xyz');
insert into mytab values (11, 'ssss');
insert into mytab values (12, 'mno');


This is quite clearly homework, and we do not do people's homework for them. We will, however, help you along provided you put in the effort.
Cheers
Re: Trick question [message #343640 is a reply to message #343635] Wed, 27 August 2008 13:13 Go to previous messageGo to next message
alok416
Messages: 7
Registered: June 2005
Location: India
Junior Member



I've describde all of the result set in my post. mind you this is not a home work, but this is something, a was asked in the interview. I just needed to know, what can i come up with , which can produce the reult set discussed in my last post.


hare krishna
Alok
Re: Trick question [message #343641 is a reply to message #343553] Wed, 27 August 2008 13:15 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>this is something, a was asked in the interview.
For what type of position?
Re: Trick question [message #343645 is a reply to message #343641] Wed, 27 August 2008 13:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If I had been asked this question in the interview, I would have asked who the hell came up with the stupid idea to store the AGE in the database.

(What are they going to do, increment it each year?? By the way, that would have been the first comment I would have liked to hear from the interviewee if I had been conducting the interview. Very Happy )

Other than that, I would use the RANK() function in it's analytic form to rank the timestamps insied the required grouping in descending order and then just select the rows with rank 1.



[Updated on: Wed, 27 August 2008 13:27]

Report message to a moderator

Re: Trick question [message #343673 is a reply to message #343640] Wed, 27 August 2008 16:01 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
alok416 wrote on Wed, 27 August 2008 19:13


I've describde all of the result set in my post.


OK, don't post a test case. That is up to you. good luck with getting an answer.
Re: Trick question [message #343978 is a reply to message #343553] Thu, 28 August 2008 09:22 Go to previous messageGo to next message
mema
Messages: 2
Registered: August 2008
Location: USA
Junior Member
Does anyone have a answer to the question asked, what sql or pl/sql to use to get a desired results?

Query Required

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

answer should be:

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‎


TABLES:

SQL> select user_id,email,gender,age,name from users;‎

‎ USER_ID EMAIL G AGE NAME‎
‎---------- ------------------------------ - ---------- ----------‎
‎ 1 a@a.com m 30 ‎ rob
‎ 2 a@a.com m 31 ‎ robert
‎ 3 b@b.com f 18‎ ‎ lucie‎
‎ 4 b@b.com f 22‎ ‎ lulu‎
‎ 5 c@c.com m 10‎ kim
‎ 6 c@c.com f 18‎ ‎ kim‎
‎ 7 c@c.com f 8 ‎ kim
‎ 8 d@d.com f 18‎ ‎ jj‎
‎ 9 d@d.com m 22‎ ‎ jay‎
‎ 10 e@e.com f 88 ‎ bill
‎ 11 e@e.com f 88‎ ‎ will‎

‎ USER_ID EMAIL G AGE NAME
‎---------- ------------------------------ - ---------- ----------‎
‎ 12 e@e.com f 60 ‎ will
‎ 13 f@f.com m 70‎ ‎ george‎

‎13 rows selected.‎



SQL> select subscription_id,user_id,subscription_type,active_indicator from ‎subscriptions;‎

SUBSCRIPTION_ID USER_ID SUBSCRIPTI ACT
‎--------------- ---------- ---------- ---‎
‎ 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‎

‎8 rows selected.‎

SQL> select subscription_id,action,timestamp from transactions;‎

SUBSCRIPTION_ID ACTION TIMESTAMP
‎--------------- --------------- ---------‎
‎ 1 ‎ renewal 10-SEP-02‎
‎ 2 ‎ cancellation 01-FEB-02‎
‎ 2 ‎ renewal 01-JAN-02‎
‎ 3 ‎ renewal 20-AUG-02‎
‎ 4‎ ‎ renewal 01-AUG-02‎
‎ 4 ‎ renewal 01-SEP-02‎
‎ 5 ‎ renewal 01-AUG-02‎
‎ 6‎ ‎ renewal 01-SEP-01‎
‎ 7 ‎ cancellation 10-SEP-02‎
‎ 7 ‎ renewal 01-SEP-02‎

‎10 rows selected.‎


Table USERS ‎
‎ ‎
columns: ‎
User_id (PK) (generated via a sequence) ‎
Email ‎
Gender ‎
Age ‎
Name ‎
‎ ‎
table SUBSCRIPTIONS ‎
columns: ‎
SUbscription_id (PK) (generated via a sequence) ‎
user_id (UK) (FK from users) ‎
subscription_type (UK) ‎
active_indicator ‎
‎ ‎
table TRANSACTIONS ‎
columns: ‎
subscription_id (PK) (FK from subscriptions) ‎
action (PK) ‎
timestamp (PK) ‎


Re: Trick question [message #343982 is a reply to message #343673] Thu, 28 August 2008 09:25 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
pablolee wrote on Wed, 27 August 2008 23:01

OK, don't post a test case. That is up to you. good luck with getting an answer.


Re: Trick question [message #343984 is a reply to message #343982] Thu, 28 August 2008 09:33 Go to previous messageGo to next message
mema
Messages: 2
Registered: August 2008
Location: USA
Junior Member
i just want to know the sql function I can use to pick a record with a highest number in a age column within a group of records
Re: Trick question [message #343985 is a reply to message #343645] Thu, 28 August 2008 09:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ThomasG wrote on Wed, 27 August 2008 20:24

Other than that, I would use the RANK() function in it's analytic form to rank the timestamps insied the required grouping in descending order and then just select the rows with rank 1.


Re: Trick question [message #343996 is a reply to message #343978] Thu, 28 August 2008 09:52 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Well, It's been answered here. Check this link.

http://forums.oracle.com/forums/thread.jspa?threadID=699437&tstart=0

Regards

Raj
Previous Topic: query to get nulll rows in between existing rows [merged]
Next Topic: Heterogeneous Services datatype conversion error
Goto Forum:
  


Current Time: Wed Dec 07 13:00:03 CST 2016

Total time taken to generate the page: 0.08256 seconds