Question: Top Search Keywords
"Using a search_logs table (user_id, keyword), write a query to find the top 10 most searched keywords."

Step-by-Step Explanation:

You have a table called search_logs with:

• user_id
• keyword

You need to:

1. Find out how many times each keyword was searched.
2. Return the top 10 keywords by search frequency.

Sample Data (search_logs):

Step 1: Group by Keyword

Group the data by keyword, so we can count how many times each keyword appears.

Example:

• "shoes" → 3 times
• "jackets" → 2 times
• "laptops" → 1 time

Step 2: Count the Number of Times Each Keyword Appeared

Use COUNT(*) to count how many times each keyword was searched.

Step 3: Sort by the Count in Descending Order

We want the most searched keywords first, so we need to ORDER BY count DESC.

Step 4: Limit to Top 10 Keywords

Use LIMIT 10 to get only the top 10 keywords.

SQL Query:

SELECT 
  keyword,
  COUNT(*) AS search_count
FROM 
  search_logs
GROUP BY 
  keyword
ORDER BY 
  search_count DESC
LIMIT 10;

Explanation of SQL:

• GROUP BY keyword groups all searches for the same keyword together.
• COUNT(*) counts how many times each keyword was searched.
• ORDER BY search_count DESC sorts the results with the most popular keywords at the top.
• LIMIT 10 restricts the result to the top 10 keywords only.

Expected Output:

(If you had more keywords, it would list the top 10.)

Important Points:

• Always group by the keyword, not by user_id.
• COUNT(*) counts all rows for each keyword.
• If two keywords have the same count, the order between them could be random unless you add a secondary ORDER BY keyword ASC.