Question

Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand.

https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true

On first trial, I approached to the solution with WITH . And I looked over the discussion page as submitted the solution. ‘Oh, Subquery also can be a key to the answer’ I thought. ‘There are many way to skin the cat but which one is more make sense. ’ So let’s figure it out.

Solution

What are Subquery and CTE?

What is SubQuery

A subquery is a query nested within another SQL query. It can return a single value, a list of values, or an entire table, depending on how it is used.

What is CTE

CTEs help make complex queries more readable and maintainable by breaking them into simpler parts. They are defined using the WITH keyword. It can return an entire table.

Feature	Subquery 🧐	CTE 🚀
Readability	Harder to read inside long queries	Easier to read and organize
Reusability	Can’t reuse results	Can be used multiple times
Performance	Can be slow if used multiple times	Better for complex queries
Use Case	One-time lookup inside a query	Temporary table-like structure

What should I choose?

In this case, somehow the query with Subquery seems much simple. Also, there’s no repetition. So…..Subquery could be the better approach for this question? On the other hand, the query with CTE might be beneficial in cases where readability and maintainability are a priority.

I think the answer is up to ‘What would Hermione wanna do with this query’. HAHA. If this is for the Inventory management program for Olivander's and there are millions of data about wands then CTE could be an better option. However, if this is just a quick lookup for a few wands, a subquery might be the simpler and more efficient choice.

Today I’ve Learned

Difference between Subquery and CTE
Through this post, I was able to clearly understand the difference between subqueries and the WITH clause. I plan to use subqueries mainly for simple queries or filtering and utilize CTEs when reusability is needed.
Definition of ‘repetition’ in SQL
I also learned what repetition truly means in SQL. Previously, I only thought about code repetition, but I realized that repeated calculations can occur within a query as well.

SELECT 
    EmployeeID, 
    (
    SELECT AVG(Salary) 
    FROM HumanResources.Employee
    ) AS AvgSalary 
FROM HumanResources.Employee;  

--Adventure Works 2019

For example, in the query above, if there are a million employees, the average salary calculation would be performed repeatedly for each employee. However, by using a CTE to store the value separately, the calculation would only be executed once. This was a great opportunity to think not only about repetition in queries but also about repetition in execution.

[SQL] HackerRank: Ollivander's Inventory (With VS SubQuery)

Table of contents