Need help on query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help on query

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2009-05-26 : 12:40:54

Dear gurus,

I need a help to write some queries.Please help me

this is the real scenario.it is not an interview question.

1) An internet search company, tracks all search activity on it's site, through the table named "table_search".This table has 2 fields: 'search_keyword' and 'search_timestamp'.
The field search_keyword records the search string, used by the user.
The field search_timestamp records the date-time of when the search was made by the user.

i.Write the SQL to generate, the list of keywords with more than 5000 searches on 15-Jan-2009.

ii. Write the SQL to generate, the list of top 100 most searched keywords in Q4 of 2008.

2) Company ABC runs 3 SMS Marketing Campaigns, with 1 SMS per Campaign, asking the targeted customers to respond by texting back 'YES' on '646'.
It tracks the sms-sent, and sms-recieved in these 2 tables:

1. table_sent. The fields are: campaign_name, customer_phone_num, sent_timestamp
2. table_response. The fields are: responding_phone_num, recieved_timestamp

If the response to an SMS is recieved within 6 hours of sending it, it is considered to be a successful response.

Write the SQL to generate the number of successful responses per campaign.

Thanks in advance
Krishnakumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 12:45:21

these are straight forward queries!
are you expecting somebody to spoon feed you with answers? before that, can you show what your tried till now?

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2009-05-26 : 12:51:26

I am not asking you to do spoonfeed. If you are interested post the answer otherwise leave it.

very good response man

good bye!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 13:06:35

you can get the query you want by using GROUP BY and HAVING. look into books online for example usage

abdou_kadri
Starting Member

13 Posts

Posted - 2009-05-26 : 13:24:35

Here you go with the first 2 queries
i.


select search_keyword 
from table_search
where search_timestamp='2009-01-15'
group by search_keyword
having count(search_keyword)>5000

ii.


select top 100 search_keyword 
from table_search
where year(search_timestamp)=2008
group by search_keyword
order by count(search_keyword) desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 13:30:36

quote:

Originally posted by abdou_kadri

Here you go with the first 2 queries
i.


select search_keyword 
from table_search
where search_timestamp='2009-01-15'
group by search_keyword
having count(search_keyword)>5000

ii.


select top 100 search_keyword 
from table_search
where year(search_timestamp)=2008
group by search_keyword
order by count(search_keyword) desc

have you tested this? you're grouping by search_keyword and taking count(search_keyword). wouldnt this always return 1?

abdou_kadri
Starting Member

13 Posts

Posted - 2009-05-26 : 13:42:28

yup tested it and works fine
if you try

select search_keyword ,count(search_keyword)
from table_search
group by search_keyword

this returns all search_keywords qrouped and there search times

and now limit results of the previous query by adding where and having clauses:

select search_keyword 
from table_search
where search_timestamp='2009-01-15'
group by search_keyword
having count(search_keyword)>5000

quote:
Originally posted by visakh16

quote:
Originally posted by abdou_kadri

Here you go with the first 2 queries
i.

select search_keyword
from table_search
where search_timestamp='2009-01-15'
group by search_keyword
having count(search_keyword)>5000

ii.

select top 100 search_keyword
from table_search
where year(search_timestamp)=2008
group by search_keyword
order by count(search_keyword) desc

have you tested this? you're grouping by search_keyword and taking count(search_keyword). wouldnt this always return 1?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources