Question: Search Query Trends
"Analyze how search query volumes change over time (daily, weekly, or monthly). Write a query that shows the number of searches over each time period."

Step-by-Step Explanation:

You have a table called search_logs with:

  • user_id
  • keyword
  • search_time (timestamp when the search was made)

You need to:

  1. Analyze how many searches were made over time periods (daily, weekly, or monthly).
  2. Show the number of searches per time period.

Sample Data (search_logs):

user_idkeywordsearch_time
1"shoes"2024-01-01 10:00:00
2"jackets"2024-01-01 11:00:00
3"shoes"2024-01-02 09:00:00
1"laptops"2024-01-02 13:00:00
2"shoes"2024-01-08 15:00:00

Step 1: Extract Time Period from Timestamp

Depending on the analysis you want:

  • Daily: Extract only the date (without time).
  • Weekly: Extract the week number and year.
  • Monthly: Extract the year and month.

In SQL, we use:

  • DATE(search_time) → gives just the date.
  • YEAR(search_time) and WEEK(search_time) → for weekly grouping.
  • DATE_FORMAT(search_time, '%Y-%m') → for monthly grouping.

Step 2: Count the Number of Searches

For each time period, count how many searches were made (COUNT(*)).

Step 3: Group by the Time Period

Use GROUP BY based on how you extract the time period.

SQL Query Examples:

1. Search Volume Daily:

SELECT
DATE(search_time) AS search_date,
COUNT(*) AS search_count
FROM
search_logs
GROUP BY
search_date
ORDER BY
search_date;

This will show the number of searches for each date.

2. Search Volume Weekly:

SELECT
YEAR(search_time) AS year,
WEEK(search_time) AS week_number,
COUNT(*) AS search_count
FROM
search_logs
GROUP BY
year, week_number
ORDER BY
year, week_number;

This will show number of searches per week.

3. Search Volume Monthly:

SELECT
DATE_FORMAT(search_time, '%Y-%m') AS month,
COUNT(*) AS search_count
FROM
search_logs
GROUP BY
month
ORDER BY
month;

This will show number of searches per month like "2024-01", "2024-02", etc.

Expected Outputs:

For Daily Search Trends:

search_datesearch_count
2024-01-012
2024-01-022
2024-01-081

For Weekly Search Trends:

yearweek_numbersearch_count
202414
202421

For Monthly Search Trends:

monthsearch_count
2024-015

Important Points:

  • Always think about which time frame is most useful (daily/weekly/monthly).
  • DATE(), WEEK(), YEAR(), and DATE_FORMAT() are the main functions used.
  • ORDER BY the time period so you can easily see the trend in the correct order.
  • Weekly can be tricky because WEEK() sometimes depends on the SQL server settings (whether week starts on Sunday or Monday).