Count Bugs Raised in the Last 7 Days

To count the number of bugs raised in the last seven days, use the COUNT() function along with a WHERE clause that filters records based on the bug creation date.

Interview Answer

"To count bugs raised in the last seven days, I use the COUNT() function with a date filter in the WHERE clause. This returns the total number of bug records created during the last week."

SQL Query

 
SELECT COUNT(*) AS total_bugs_last_7_days
FROM bugs
WHERE raised_date >= CURRENT_DATE - INTERVAL 7 DAY;
 

Query Explanation

  • COUNT(*) counts the total number of bug records.
  • AS total_bugs_last_7_days gives a meaningful name to the result.
  • CURRENT_DATE - INTERVAL 7 DAY filters records created within the last seven days.

Useful Variations

Count Bugs Per Day

 
SELECT raised_date,
       COUNT(*) AS bug_count
FROM bugs
WHERE raised_date >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY raised_date;
 

Count Only Open Bugs

 
SELECT COUNT(*) AS open_bugs
FROM bugs
WHERE raised_date >= CURRENT_DATE - INTERVAL 7 DAY
AND bug_status = 'Open';
 

Real-Time Example

In one project, we generated a weekly defect report by counting all bugs raised during the last seven days and grouping them by date to monitor testing progress.

Advertisement

Find Users Who Didn't Log In Today

Finding users who did not log in is a common SQL interview question. It can be solved using either a NOT IN subquery or a LEFT JOIN.

Interview Answer

"I typically use a LEFT JOIN with IS NULL to identify users who haven't logged in today because it handles NULL values more reliably than a NOT IN subquery."

Option 1 – Using NOT IN

 
SELECT *
FROM users
WHERE user_id NOT IN (
    SELECT user_id
    FROM logins
    WHERE login_date = CURRENT_DATE
);
 
 
SELECT u.*
FROM users u
LEFT JOIN logins l
       ON u.user_id = l.user_id
      AND l.login_date = CURRENT_DATE
WHERE l.user_id IS NULL;
 

Why LEFT JOIN Is Preferred

  • Handles NULL values correctly.
  • Better suited for large datasets.
  • Easier to extend with additional conditions.

Real-Time Example

During user activity reporting, I used a LEFT JOIN to identify users who had not logged into the application that day so reminder emails could be sent.

Fetch Test Cases for a Specific Module

To retrieve test cases belonging to a particular application module, use the WHERE clause.

Interview Answer

"To fetch test cases for a specific module, I filter the test_cases table using the module name in the WHERE clause."

SQL Query

 
SELECT *
FROM test_cases
WHERE module_name = 'Login';
 

Useful Variations

Fetch Multiple Modules

 
SELECT *
FROM test_cases
WHERE module_name IN ('Login', 'Payment');
 

Ignore Case

 
SELECT *
FROM test_cases
WHERE LOWER(module_name) = 'login';
 

Fetch Only Active Test Cases

 
SELECT *
FROM test_cases
WHERE module_name = 'Login'
AND status = 'Active';
 

Real-Time Example

Before regression testing, I retrieved all active Login module test cases from the database to verify execution coverage.

Count Bugs Assigned to Each Developer

To determine the workload of each developer, group bugs by the assigned developer and count them.

Interview Answer

"I use GROUP BY with the COUNT() function to calculate how many bugs are assigned to each developer."

SQL Query

 
SELECT assigned_to,
       COUNT(*) AS bug_count
FROM bugs
GROUP BY assigned_to;
 

Useful Variations

Sort by Highest Bug Count

 
SELECT assigned_to,
       COUNT(*) AS bug_count
FROM bugs
GROUP BY assigned_to
ORDER BY bug_count DESC;
 

Show Unassigned Bugs

 
SELECT COALESCE(assigned_to, 'Unassigned') AS developer,
       COUNT(*) AS bug_count
FROM bugs
GROUP BY assigned_to;
 

Count Bugs Raised in the Last 30 Days

 
SELECT assigned_to,
       COUNT(*) AS bug_count
FROM bugs
WHERE raised_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY assigned_to;
 

Real-Time Example

We used this query to generate weekly workload reports showing how many bugs were assigned to each developer.

Find the Highest and Lowest Bug Severity Count

This query determines which bug severity has the highest or lowest number of defects.

Interview Answer

"I first group bugs by severity using GROUP BY, count each severity level, and then sort the results or use MAX() and MIN() to identify the highest and lowest counts."

Step 1 – Count Bugs by Severity

 
SELECT severity,
       COUNT(*) AS bug_count
FROM bugs
GROUP BY severity;
 

Step 2 – Highest Severity Count

 
SELECT severity,
       COUNT(*) AS bug_count
FROM bugs
GROUP BY severity
ORDER BY bug_count DESC
LIMIT 1;
 

Step 3 – Lowest Severity Count

 
SELECT severity,
       COUNT(*) AS bug_count
FROM bugs
GROUP BY severity
ORDER BY bug_count ASC
LIMIT 1;
 

Alternative Using MAX() and MIN()

 
SELECT
    MAX(bug_counts) AS highest_bug_count,
    MIN(bug_counts) AS lowest_bug_count
FROM (
    SELECT COUNT(*) AS bug_counts
    FROM bugs
    GROUP BY severity
) t;
 

Notes

  • LIMIT 1 returns only one severity even if multiple severities have the same count.
  • Add WHERE conditions to filter by date, project, or bug status.
  • The same approach can be used to count passed, failed, or blocked test cases.

Real-Time Example

During release reporting, we analyzed defect distribution by severity to identify whether Critical or High severity bugs required immediate attention.

Frequently Asked Questions (FAQs)

1. How do you count bugs raised in the last 7 days?

Use the COUNT() function with a date filter.

 
SELECT COUNT(*)
FROM bugs
WHERE raised_date >= CURRENT_DATE - INTERVAL 7 DAY;
 

For a daily breakdown, group the results by raised_date.

2. How do you find users who didn't log in today?

You can use either:

  • A NOT IN subquery.
  • A LEFT JOIN with IS NULL (recommended because it handles NULL values correctly).

3. How do you fetch test cases for a specific module?

Filter the table using the module name.

 
SELECT *
FROM test_cases
WHERE module_name = 'Login';
 

Use:

  • IN for multiple modules.
  • LOWER() or LIKE for case-insensitive searches.

4. How do you count bugs assigned to each developer?

Group the records by developer and use the COUNT() function.

 
SELECT assigned_to,
       COUNT(*) AS bug_count
FROM bugs
GROUP BY assigned_to;
 

Use ORDER BY bug_count DESC to display developers with the highest number of assigned bugs first.

5. How do you find the severity with the most bugs?

Group by severity, count the bugs, and sort the results in descending order.

 
SELECT severity,
       COUNT(*) AS bug_count
FROM bugs
GROUP BY severity
ORDER BY bug_count DESC
LIMIT 1;
 

You can also use MAX() or MIN() on grouped counts to determine the highest or lowest bug totals.

6. Why is LEFT JOIN preferred over NOT IN for "Not Logged In" queries?

LEFT JOIN ... IS NULL is generally preferred because it handles NULL values correctly.

The NOT IN operator may return unexpected results if the subquery contains NULL values, whereas a LEFT JOIN with an IS NULL condition reliably returns records that have no matching entries in the related table.