CODEWITHSUNDEEP
sqlsql-server
user9022829
user9022829

Reputation:

SQL most effective query

I'm creating two queries on the 3 tables below and I just want someone's opinion on if I'm executing them most effectively.

User (**user_id**, group, age, gender, timestamp, activity_type)

Client (**client_id**, sector, specified_age, specified_gender)

Advertise (a**d_id, user_id, client_id, time**, is_click, length)

Which client sector had the most advertisement clicks?

 SELECT c.client_id, c.sector, COUNT(a.is_click) as Clicked_Ads 
    FROM Advertise a, Client c
    WHERE a.client_id = c.client_id
    GROUP BY COUNT(a.is_click)
    ORDER BY c.client_id

What time are women most frequently watching advertisements?

 SELECT a.ad_id, a.client_id, u.user_id, u.timestamps, COUNT(u.timestamps)
    FROM Advertise a, User u
    WHERE u.gender = “Female”, a.user_id = u.user_id
    ORDER BY 
    GROUP BY COUNT(u.timestamps

)

Upvotes: 2

Views: 50

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

To find client sector with most clicks across all clients, for MS SQL server

SELECT TOP 1 WITH TIES client_id,sector, Clicked_Ads 
 (
   SELECT c.client_id, c.sector, SUM(a.is_click) as Clicked_Ads 
    FROM Advertise a JOIN Client c
    ON a.client_id = c.client_id
    GROUP BY c.client_id, c.sector
  )T
ORDER BY Clicked_Ads  DESC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Your first query should look like this:

SELECT c.sector, COUNT(a.is_click) as Clicked_Ads 
FROM Advertise a JOIN
     Client c
     ON a.client_id = c.client_id
GROUP BY c.sector
ORDER BY COUNT(a.is_click) DESC
LIMIT 1;

Notes:

  • Use proper, explicit JOIN syntax. Never use commas in the FROM clause.
  • The question is about sector, nothing else is needed in the GROUP BY clause.
  • You cannot GROUP BY an aggregation function anyway.
  • Presumably you want the ORDER BY and LIMIT 1 to get the top sector.

One question is enough for a question.

Upvotes: 1

Related Questions