Trick question [message #343553] |
Wed, 27 August 2008 09:11  |
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 #343627 is a reply to message #343553] |
Wed, 27 August 2008 12:08   |
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   |
pablolee
Messages: 2882 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 #343645 is a reply to message #343641] |
Wed, 27 August 2008 13:24   |
ThomasG
Messages: 3212 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. )
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   |
pablolee
Messages: 2882 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   |
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   |
ThomasG
Messages: 3212 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   |
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   |
ThomasG
Messages: 3212 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.
|
|
|
|
|