Tuesday, November 6, 2012

SQL Questions

Date: 06-Nov-2012

SQL: Inner / Sub query group wise problem

This blog contains questions which are different in problem solving approach and related to IT world. Origin is from day to day discussion / interview / other findings. 

Question:

Consider a schema

ID | Category | Keyword | Bid Price

Write a sql to fetch out top 5 keywords based on the bid price per category.
No RDBMS functions, only SQL.


Details/assumptions:

Table definition considered on oracle 10.x
create table test (
ID number,
Category varchar (20),
Keyword varchar (20),
BidPrice number
);

Data:

insert into test values (1, 'Category-A', 'Keyword-A1', 110);
insert into test values (2, 'Category-A', 'Keyword-A2', 121);
insert into test values (3, 'Category-A', 'Keyword-A3', 130);
insert into test values (4, 'Category-A', 'Keyword-A4', 125);
insert into test values (5, 'Category-A', 'Keyword-A5', 115);
insert into test values (6, 'Category-A', 'Keyword-A6', 133);
insert into test values (7, 'Category-B', 'Keyword-B1', 105);
insert into test values (8, 'Category-B', 'Keyword-B2', 111);
insert into test values (9, 'Category-B', 'Keyword-B3', 108);
insert into test values (10, 'Category-B', 'Keyword-B4', 128);
insert into test values (11, 'Category-B', 'Keyword-B5', 144);
insert into test values (12, 'Category-B', 'Keyword-B6', 101);
insert into test values (13, 'Category-C', 'Keyword-C1', 150);
insert into test values (14, 'Category-C', 'Keyword-C2', 137);
insert into test values (15, 'Category-C', 'Keyword-C3', 126);
insert into test values (16, 'Category-C', 'Keyword-C4', 121);
insert into test values (17, 'Category-C', 'Keyword-C5', 112);
insert into test values (18, 'Category-C', 'Keyword-C6', 118);




Output expected:

KEYWORD       CATEGORY     BIDPRICE
------------- ------------- ----------
Keyword-A3      Category-A   130
Keyword-A4      Category-A   125
Keyword-A2      Category-A   121
Keyword-A5      Category-A   115
Keyword-A1      Category-A   110
Keyword-B5      Category-B   144
Keyword-B4      Category-B   128
Keyword-B2      Category-B   111
Keyword-B3      Category-B   108
Keyword-B1      Category-B   105
Keyword-C1      Category-C   150
Keyword-C2      Category-C   137
Keyword-C3      Category-C   126
Keyword-C4      Category-C   121
Keyword-C5      Category-C   112


Answer:

Till this point all the provided ingredients are enough to cook our sql for a given scenario.  Still if you do not want to try your own and want to see result have a look:

Oracle specific solution:

SELECT KEYWORD ,CATEGORY, BIDPRICE
FROM
(
  SELECT KEYWORD ,CATEGORY, BIDPRICE,
          ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY BIDPRICE DESC) rn
  FROM test
) a
WHERE rn <= 5
ORDER BY CATEGORY, BIDPRICE DESC


Final Solution:
Below is the only SQL solution which is accepted as a final solution is:

SELECT 
    A.KEYWORD, A.CATEGORY, A.BIDPRICE 
FROM TEST A,  (
        SELECT 
            B.ID, B.CATEGORY 
        FROM TEST B 
        WHERE 
        (
            SELECT 
                DISTINCT(C.CATEGORY) 
            FROM TEST C 
            WHERE 
                B.CATEGORY=C.CATEGORY
        ) = B.CATEGORY 
        ORDER BY B.BIDPRICE DESC
    ) D WHERE A.ID IN 
        (SELECT ID FROM 
            (SELECT E.ID, E.CATEGORY FROM TEST E ORDER BY E.BIDPRICEDESC) F 
        WHERE A.CATEGORY=F.CATEGORY AND ROWNUM<=5
    ) 
GROUP BY A.CATEGORY, A.BIDPRICE, A.KEYWORD ORDER BY A.CATEGORY, A.BIDPRICE DESC;




Please let me know if you have any question.

Thanks
Shailendra 

No comments:

Post a Comment