Asked in: INFOTECH
Image of the Question
All Testcases Passed ✔
Solution
SELECT
p.company,
COALESCE(SUM(CASE WHEN i.score >= 4 THEN i.score ELSE 0 END), 0) AS total_success_score,
COALESCE(COUNT(CASE WHEN i.score >= 4 THEN 1 ELSE NULL END), 0) AS successful_interviews
// ... rest of solution available after purchaseExplanation
```
To approach this problem, start by understanding the relationship between the two tables involved: `interviews` and `positions`. The `interviews` table captures individual interview sessions, including which position each interview was for (via `position_id`) and the result of the interview as a score from 1 to 5. The `positions` table gives more context about each position, specifically linking it to a company.
The key to solving this problem lies in correctly joining these two tables and filtering the data according to the success criteria—interviews with a score of 4 or higher. You should first think about how to associate each interview with the company it relates to. This requires an inner or left join between the `interviews` and `positions` tables, using `interviews.position_id = positions.id` as the join condition. This join ensures that you can access company-level data while aggregating interview-level data.
Once you have successfully joined the tables, focus on identifying successful interviews. This means you need to examine the `score` column and determine which rows have scores greater than or equal to 4. Only these interviews are considered successful. However, since the requirement explicitly states that companies with no successful interviews should still be returned (with a count and sum of 0), you need to structure your query in a way that does not exclude such companies entirely. This suggests that a left join or some form of aggregation that allows for NULL handling may be necessary.
A common pattern for such a problem is to group by the company name, since the final results should be aggregated per company. When grouping by company, for each group, you’ll want to calculate two values:
1. The total number of successful interviews (i.e., how many interview scores are greater than or equal to 4)
2. The sum of scores from those successful interviews
When calculating these aggregates, be mindful of the need to include companies with zero successful interviews. To handle this correctly, you should think about applying conditional aggregation. That means using expressions that evaluate conditions (e.g., “score >= 4”) and summing or counting based on whether those conditions are true. This way, even if a company has interview entries but none of them are successful, the result will still show 0 instead of excluding the company.
It's also possible that some companies have positions but no interviews at all. If that scenario is valid in your data, you need to account for it by using a left join from the `positions` table to the `interviews` table, ensuring that all companies are represented in the result—even if they had no interviews recorded. If your data guarantees that every position had at least one interview, this might not be necessary, but it’s safer to consider the possibility.
Next, think about how to structure the conditional logic. For counting successful interviews, you can use a CASE expression inside a COUNT or SUM function, incrementing only when the score meets the threshold. Similarly, for the total score, use a CASE expression inside a SUM function that adds the score only when it is 4 or higher, and adds 0 otherwise.
Finally, once you've grouped the data and computed the count and sum for each company, sort the output by company name in ascending order. This is straightforward but important to fulfill the presentation requirement.
Throughout this approach, be sure to handle NULL values properly. When no successful interviews exist for a company, the aggregation should still return 0, not NULL. You may need to wrap the aggregations with functions or default values to ensure the final output adheres to this.
In summary, your approach should follow these major steps:
1. Join the `interviews` and `positions` tables on position ID.
2. Group the result by company.
3. Use conditional aggregation to count and sum only the successful interviews.
4. Ensure companies with no successful interviews are included with 0s.
5. Order the result by company name in ascending order.
By thinking in terms of joins, grouping, conditional logic, and sorting, you will be able to construct a query that accurately reflects the requirements and handles all edge cases properly.
```