![\"Most](\"https:\/\/www.kdnuggets.com\/wp-content\/uploads\/Rosidi-Most_Candidates_Fail_These_SQL_Concepts-1.png\"\/)
![\"Most](\"https:\/\/www.kdnuggets.com\/wp-content\/uploads\/Rosidi-Most_Candidates_Fail_These_SQL_Concepts-1.png\")
Picture by writer | Canva<\/span><\/center>
\n\u00a0<\/p>\n
An interviewer’s job is to search out essentially the most appropriate candidates for the marketed place. In doing so, they are going to gladly arrange SQL interview inquiries to see if they’ll catch you off guard. There are a number of SQL ideas at which candidates typically fail.<\/p>\n
Hopefully, you\u2019ll be a type of who keep away from that future, as I\u2019ll clarify these ideas intimately under, full with examples of easy methods to resolve sure issues appropriately.<\/p>\n
\u00a0![\"Most](\"https:\/\/www.kdnuggets.com\/wp-content\/uploads\/Rosidi-Most_Candidates_Fail_These_SQL_Concepts-2-scaled.png\"\/)
![\"Most](\"https:\/\/www.kdnuggets.com\/wp-content\/uploads\/Rosidi-Most_Candidates_Fail_These_SQL_Concepts-2-scaled.png\")
\u00a0<\/p>\n
#\u00a0<\/span>1. Window Capabilities<\/h2>\n\u00a0
Why It\u2019s Laborious: Candidates memorize what every window perform<\/a><\/strong> does however don\u2019t actually perceive how window frames, partitions, or ordering truly work.<\/p>\nWidespread Errors: A standard mistake shouldn’t be specifying ORDER BY<\/code> in rating window capabilities or worth window capabilities, similar to LEAD()<\/code> or LAG()<\/code><\/a><\/strong>, and anticipating the question to work or for the end result to be deterministic.<\/p>\nInstance: In this instance,<\/a> you might want to discover customers who made a second buy inside 7 days of any earlier buy.<\/p>\nYou may write this question.<\/p>\n
\nWITH ordered_tx AS (
\n\u00a0 SELECT user_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0created_at::date AS tx_date,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0LAG(created_at::DATE) OVER (PARTITION BY user_id) AS prev_tx_date
\n\u00a0 FROM amazon_transactions
\n)
\n
\nSELECT DISTINCT user_id
\nFROM ordered_tx
\nWHERE prev_tx_date IS NOT NULL AND tx_date - prev_tx_date <= 7;<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
At first look, every little thing might sound proper. The code even outputs one thing that may seem like an accurate reply.<\/p>\n
\u00a0
![\"Window](\"https:\/\/www.kdnuggets.com\/wp-content\/uploads\/Rosidi-Most_Candidates_Fail_These_SQL_Concepts-3.1.png\"\/)
![\"Window](\"https:\/\/www.kdnuggets.com\/wp-content\/uploads\/Rosidi-Most_Candidates_Fail_These_SQL_Concepts-3.1.png\")
\u00a0<\/p>\n
To begin with, we’re fortunate that the code works in any respect! This occurs just because I\u2019m writing it in PostgreSQL<\/a><\/strong>. In another SQL flavors, you\u2019d get an error since ORDER BY<\/code> is necessary in rating and analytical window capabilities.<\/p>\nSecond, the output is unsuitable; I highlighted some rows that shouldn\u2019t be there. Why do they seem, then?<\/p>\n
They seem as a result of we didn\u2019t specify an ORDER BY<\/code> clause within the LAG()<\/code> window perform. With out it, the row order is bigoted. So, we’re evaluating the present transaction to some random earlier row for that person, not the one which occurred instantly earlier than it in time.<\/p>\nThis isn’t what the query asks. We have to evaluate every transaction to the earlier one by date. In different phrases, we have to specify this explicitly within the ORDER BY<\/code> clause throughout the LAG()<\/code> perform.<\/p>\n\nWITH ordered_tx AS (
\n\u00a0 SELECT user_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0created_at::date AS tx_date,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0LAG(created_at::DATE) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_tx_date
\n\u00a0 FROM amazon_transactions
\n)
\n
\nSELECT DISTINCT user_id
\nFROM ordered_tx
\nWHERE prev_tx_date IS NOT NULL AND tx_date - prev_tx_date <= 7;<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
#\u00a0<\/span>2. Filtering With Aggregates (Particularly HAVING vs. WHERE)<\/h2>\n\u00a0
Why It\u2019s Laborious: Folks typically don\u2019t perceive the execution order in SQL, which is: FROM<\/code> -> WHERE<\/code> -> GROUP BY<\/code> -> HAVING<\/code> -> SELECT<\/code> -> ORDER BY<\/code>. This order implies that WHERE<\/code> filters rows earlier than aggregation, and HAVING<\/code> filters after<\/a><\/strong>. That additionally, logically, means that you would be able to\u2019t use combination capabilities within the WHERE<\/code> clause.<\/p>\nWidespread Mistake: Making an attempt to make use of combination capabilities in WHERE<\/code> in a grouped question and getting an error.<\/p>\nInstance: This interview query<\/a> asks you to search out the whole income made by every vineyard. Solely wineries the place 90 is the bottom variety of factors for any of their varieties needs to be thought of.<\/p>\nMany will see this as a simple query and unexpectedly write this question.<\/p>\n
\nSELECT vineyard,
\n\u00a0 \u00a0 \u00a0 \u00a0selection,
\n\u00a0 \u00a0 \u00a0 \u00a0SUM(worth) AS total_revenue
\nFROM winemag_p1
\nWHERE MIN(factors) >= 90
\nGROUP BY vineyard, selection
\nORDER BY vineyard, total_revenue DESC;<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
Nonetheless, that code will throw an error stating that combination capabilities will not be allowed within the WHERE<\/code> clause. This beautiful a lot explains every little thing. The answer? Transfer the filtering situation from WHERE<\/code> to HAVING<\/code>.<\/p>\n\nSELECT vineyard,
\n\u00a0 \u00a0 \u00a0 \u00a0selection,
\n\u00a0 \u00a0 \u00a0 \u00a0SUM(worth) AS total_revenue
\nFROM winemag_p1
\nGROUP BY vineyard, selection
\nHAVING MIN(factors) >= 90
\nORDER BY vineyard, total_revenue DESC;<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
#\u00a0<\/span>3. Self-Joins for Time-Based mostly or Occasion-Based mostly Comparisons<\/h2>\n\u00a0
Why It\u2019s Laborious: The thought of becoming a member of a desk with itself<\/a><\/strong> is sort of unintuitive, so candidates typically neglect it\u2019s an choice.<\/p>\nWidespread Mistake: Utilizing subqueries and complicating the question when becoming a member of a desk with itself can be less complicated and sooner, particularly when filtering by dates or occasions.<\/p>\n
Instance: Right here\u2019s a query<\/a> asking you to point out the change of each foreign money\u2019s change price between 1 January 2020 and 1 July 2020.<\/p>\nYou may resolve this by writing an outer correlated subquery that fetches the July 1 change charges, then subtracts the January 1 change charges, which come from the inside subquery.<\/p>\n
\nSELECT jan_rates.source_currency,
\n\u00a0 (SELECT exchange_rate\u00a0
\n\u00a0 \u00a0FROM sf_exchange_rate\u00a0
\n\u00a0 \u00a0WHERE source_currency = jan_rates.source_currency AND date=\"2020-07-01\") - jan_rates.exchange_rate AS distinction
\nFROM (SELECT source_currency, exchange_rate
\n\u00a0 \u00a0 \u00a0 FROM sf_exchange_rate
\n\u00a0 \u00a0 \u00a0 WHERE date=\"2020-01-01\"
\n) AS jan_rates;<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
This returns an accurate output, however such an answer is unnecessarily sophisticated. A a lot less complicated answer, with fewer strains of code, includes self-joining a desk with itself after which making use of two date filtering situations within the WHERE<\/code> clause.<\/p>\n\nSELECT jan.source_currency,
\n\u00a0 \u00a0 \u00a0 \u00a0jul.exchange_rate - jan.exchange_rate AS distinction
\nFROM sf_exchange_rate jan
\nJOIN sf_exchange_rate jul ON jan.source_currency = jul.source_currency
\nWHERE jan.date=\"2020-01-01\" AND jul.date=\"2020-07-01\";<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
#\u00a0<\/span>4. Subqueries vs. Widespread Desk Expressions (CTEs)<\/h2>\n\u00a0
Why It\u2019s Laborious: Folks typically get caught on subqueries as a result of they be taught them earlier than Widespread Desk Expressions (CTEs) and proceed utilizing them for any question with layered logic. Nonetheless, subqueries can get messy in a short time.<\/p>\n
Widespread Mistake: Utilizing deeply nested SELECT<\/code> statements when CTEs can be a lot less complicated.<\/p>\nInstance: Within the interview query<\/a> from Google and Netflix, you might want to discover the highest actors based mostly on their common film score throughout the style through which they seem most ceaselessly.<\/p>\nThe answer utilizing CTEs is as follows.<\/p>\n
\nWITH genre_stats AS
\n\u00a0 (SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0FROM top_actors_rating
\n\u00a0 \u00a0GROUP BY actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 style),
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0
\nmax_genre_count AS
\n\u00a0 (SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 MAX(movie_count) AS max_count
\n\u00a0 \u00a0FROM genre_stats
\n\u00a0 \u00a0GROUP BY actor_name),
\n\u00a0 \u00a0 \u00a0
\ntop_genres AS
\n\u00a0 (SELECT gs.*
\n\u00a0 \u00a0FROM genre_stats gs
\n\u00a0 \u00a0JOIN max_genre_count mgc ON gs.actor_name = mgc.actor_name
\n\u00a0 \u00a0AND gs.movie_count = mgc.max_count),
\n\u00a0 \u00a0 \u00a0
\ntop_genre_avg AS
\n\u00a0 (SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 MAX(avg_rating) AS max_avg_rating
\n\u00a0 \u00a0FROM top_genres
\n\u00a0 \u00a0GROUP BY actor_name),
\n\u00a0 \u00a0
\nfiltered_top_genres AS
\n\u00a0 (SELECT tg.*
\n\u00a0 \u00a0FROM top_genres tg
\n\u00a0 \u00a0JOIN top_genre_avg tga ON tg.actor_name = tga.actor_name
\n\u00a0 \u00a0AND tg.avg_rating = tga.max_avg_rating),
\n\u00a0 \u00a0 \u00a0ranked_actors AS
\n\u00a0 (SELECT *,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 DENSE_RANK() OVER (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0ORDER BY avg_rating DESC) AS rank
\n\u00a0 \u00a0FROM filtered_top_genres),
\n\u00a0 \u00a0
\nfinal_selection AS
\n\u00a0 (SELECT MAX(rank) AS max_rank
\n\u00a0 \u00a0FROM ranked_actors
\n\u00a0 \u00a0WHERE rank <= 3)
\n\u00a0 \u00a0
\nSELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0avg_rating
\nFROM ranked_actors
\nWHERE rank <=
\n\u00a0 \u00a0 (SELECT max_rank
\n\u00a0 \u00a0 \u00a0FROM final_selection);
\n<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
It’s comparatively sophisticated, but it surely nonetheless consists of six clear CTEs, with the code’s readability enhanced by clear aliases.<\/p>\n
Curious what the identical answer would appear to be utilizing solely subqueries? Right here it’s.<\/p>\n
\nSELECT ra.actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0ra.style,
\n\u00a0 \u00a0 \u00a0 \u00a0ra.avg_rating
\nFROM (
\n\u00a0 \u00a0 SELECT *,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0DENSE_RANK() OVER (ORDER BY avg_rating DESC) AS rank
\n\u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 SELECT tg.*
\n\u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT gs.*
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS gs
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(movie_count) AS max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS genre_stats
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS mgc
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ON gs.actor_name = mgc.actor_name AND gs.movie_count = mgc.max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 ) AS tg
\n\u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(avg_rating) AS max_avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT gs.*
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS gs
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(movie_count) AS max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS genre_stats
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS mgc
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ON gs.actor_name = mgc.actor_name AND gs.movie_count = mgc.max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS top_genres
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 ) AS tga
\n\u00a0 \u00a0 \u00a0 \u00a0 ON tg.actor_name = tga.actor_name AND tg.avg_rating = tga.max_avg_rating
\n\u00a0 \u00a0 ) AS filtered_top_genres
\n) AS ra
\nWHERE ra.rank <= (
\n\u00a0 \u00a0 SELECT MAX(rank)
\n\u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 SELECT *,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0DENSE_RANK() OVER (ORDER BY avg_rating DESC) AS rank
\n\u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT tg.*
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT gs.*
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS gs
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(movie_count) AS max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS genre_stats
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS mgc
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ON gs.actor_name = mgc.actor_name AND gs.movie_count = mgc.max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS tg
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(avg_rating) AS max_avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT gs.*
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS gs
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(movie_count) AS max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS genre_stats
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS mgc
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ON gs.actor_name = mgc.actor_name AND gs.movie_count = mgc.max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS top_genres
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS tga
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ON tg.actor_name = tga.actor_name AND tg.avg_rating = tga.max_avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 ) AS filtered_top_genres
\n\u00a0 \u00a0 ) AS ranked_actors
\n\u00a0 \u00a0 WHERE rank <= 3
\n);<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
There’s redundant logic repeated throughout subqueries. What number of subqueries is that? I do not know. The code is unimaginable to keep up. Though I simply wrote it, I\u2019d nonetheless want half a day to grasp it if I needed to alter one thing tomorrow. Moreover, the utterly meaningless subquery aliases don’t assist.<\/p>\n
\u00a0<\/p>\n
#\u00a0<\/span>5. Dealing with NULLs in Logic<\/h2>\n\u00a0
Why It\u2019s Laborious: Candidates typically suppose that NULL<\/code> is the same as one thing. It\u2019s not. NULL<\/code> isn\u2019t equal to something \u2014 not even itself. Logic involving NULL<\/code>s behaves in another way from logic involving precise values.<\/p>\nWidespread Mistake: Utilizing = NULL<\/code> as an alternative of IS NULL<\/code> in filtering or lacking output rows as a result of NULL<\/code>s break the situation logic.<\/p>\nInstance: There\u2019s an interview query by IBM<\/a> that asks you to calculate the whole variety of interactions and the whole variety of contents created for every buyer.<\/p>\nIt doesn\u2019t sound too difficult, so that you may write this answer with two CTEs, the place one CTE counts the variety of interactions per buyer, whereas the opposite counts the variety of content material objects created by a buyer. Within the remaining SELECT<\/code>, you FULL OUTER JOIN<\/code> the 2 CTEs, and you’ve got the answer. Proper?<\/p>\n\nWITH interactions_summary AS
\n\u00a0 (SELECT customer_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 COUNT(*) AS total_interactions
\n\u00a0 \u00a0FROM customer_interactions
\n\u00a0 \u00a0GROUP BY customer_id),
\n\u00a0 \u00a0
\ncontent_summary AS
\n\u00a0 (SELECT customer_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 COUNT(*) AS total_content_items
\n\u00a0 \u00a0FROM user_content
\n\u00a0 \u00a0GROUP BY customer_id)
\n\u00a0 \u00a0
\nSELECT i.customer_id,
\n\u00a0 i.total_interactions,
\n\u00a0 c.total_content_items
\nFROM interactions_summary AS i
\nFULL OUTER JOIN content_summary AS c ON i.customer_id = c.customer_id
\nORDER BY customer_id;<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
Virtually proper. Right here\u2019s the output. (By the best way, you see double citation marks (“”) as an alternative of NULL<\/code>. That\u2019s how the StrataScratch UI shows it, however belief me, the engine nonetheless treats them for what they’re: NULL<\/code> values).<\/p>\n\u00a0
![\"5.](\"https:\/\/www.kdnuggets.com\/wp-content\/uploads\/Rosidi-Most_Candidates_Fail_These_SQL_Concepts-4-scaled.png\"\/)
![\"5.](\"https:\/\/www.kdnuggets.com\/wp-content\/uploads\/Rosidi-Most_Candidates_Fail_These_SQL_Concepts-4-scaled.png\")
\u00a0<\/p>\n
The highlighted rows comprise NULL<\/code>s. This makes the output incorrect. A NULL<\/code> worth is neither the shopper ID nor the variety of interactions and contents, which the query explicitly asks you to point out.<\/p>\nWhat we\u2019re lacking within the above answer is COALESCE()<\/code><\/a><\/strong> to deal with NULL<\/code>s within the remaining SELECT<\/code>. Now, all the purchasers with out interactions will get their IDs from the content_summary<\/code> CTE. Additionally, for purchasers that don\u2019t have interactions, or content material, or each, we\u2019ll now exchange NULL<\/code> with 0, which is a sound quantity.<\/p>\n\nWITH interactions_summary AS
\n\u00a0 (SELECT customer_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 COUNT(*) AS total_interactions
\n\u00a0 \u00a0FROM customer_interactions
\n\u00a0 \u00a0GROUP BY customer_id),
\n\u00a0 \u00a0
\ncontent_summary AS
\n\u00a0 (SELECT customer_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 COUNT(*) AS total_content_items
\n\u00a0 \u00a0FROM user_content
\n\u00a0 \u00a0GROUP BY customer_id)
\n\u00a0 \u00a0
\nSELECT COALESCE(i.customer_id, c.customer_id) AS customer_id,
\n\u00a0 \u00a0 \u00a0 \u00a0COALESCE(i.total_interactions, 0) AS total_interactions,
\n\u00a0 \u00a0 \u00a0 \u00a0COALESCE(c.total_content_items, 0) AS total_content_items
\nFROM interactions_summary AS i
\nFULL OUTER JOIN content_summary AS c ON i.customer_id = c.customer_id
\nORDER BY customer_id;<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
#\u00a0<\/span>6. Group-Based mostly Deduplication<\/h2>\n\u00a0
Why It\u2019s Laborious: Group-based deduplication means you\u2019re choosing one row per group, e.g., \u201cmost up-to-date\u201d, \u201chighest rating\u201d, and so on. At first, it sounds such as you solely want to choose one row per person. However you may\u2019t use GROUP BY<\/code> except you combination. Alternatively, you typically want a full row, not a single worth that aggregation and GROUP BY<\/code> return.<\/p>\nWidespread Mistake: Utilizing GROUP BY<\/code> + LIMIT 1<\/code> (or DISTINCT ON<\/a><\/strong>, which is PostgreSQL-specific) as an alternative of ROW_NUMBER()<\/code> or RANK()<\/code>, the latter in order for you ties included.<\/p>\nInstance: This query<\/a> asks you to establish the best-selling merchandise for every month, and there\u2019s no have to separate months by 12 months. One of the best-selling merchandise is calculated as unitprice * amount<\/code>.<\/p>\nThe naive strategy can be this. First, extract the sale month from invoicedate<\/code>, choose description<\/code>, and discover the whole gross sales by summing unitprice * amount<\/code>. Then, to get the whole gross sales by month and product description, we merely GROUP BY<\/code> these two columns. Lastly, we solely want to make use of ORDER BY<\/code> to kind the output from the very best to the worst-selling product and use LIMIT 1<\/code> to output solely the primary row, i.e., the best-selling merchandise.<\/p>\n\nSELECT DATE_PART('MONTH', invoicedate) AS sale_month,
\n\u00a0 \u00a0 \u00a0 \u00a0description,
\n\u00a0 \u00a0 \u00a0 \u00a0SUM(unitprice * amount) AS total_paid
\nFROM online_retail
\nGROUP BY sale_month, description
\nORDER BY total_paid DESC
\nLIMIT 1;<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
As I stated, that is naive; the output considerably resembles what we’d like, however we’d like this for each month, not only one.<\/p>\n
\u00a0
![\"Group-Based](\"https:\/\/www.kdnuggets.com\/wp-content\/uploads\/Rosidi-Most_Candidates_Fail_These_SQL_Concepts-5-scaled.png\"\/)
![\"Group-Based](\"https:\/\/www.kdnuggets.com\/wp-content\/uploads\/Rosidi-Most_Candidates_Fail_These_SQL_Concepts-5-scaled.png\")
\u00a0<\/p>\n
One of many appropriate approaches is to make use of the RANK()<\/code> window perform. With this strategy, we observe an analogous technique to the earlier code. The distinction is that the question now turns into a subquery within the FROM<\/code> clause. As well as, we use RANK()<\/code> to partition the information by month after which rank the rows inside every partition (i.e., for every month individually) from the best-selling to the worst-selling merchandise.<\/p>\nThen, in the principle question, we merely choose the required columns and output solely rows the place the rank is 1 utilizing the WHERE<\/code> clause.<\/p>\n\nSELECT month,
\n\u00a0 \u00a0 \u00a0 \u00a0description,
\n\u00a0 \u00a0 \u00a0 \u00a0total_paid
\nFROM
\n\u00a0 (SELECT DATE_PART('month', invoicedate) AS month,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 description,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SUM(unitprice * amount) AS total_paid,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 RANK() OVER (PARTITION BY DATE_PART('month', invoicedate) ORDER BY SUM(unitprice * amount) DESC) AS rnk
\n\u00a0 \u00a0FROM online_retail
\n\u00a0 \u00a0GROUP BY month, description) AS tmp
\nWHERE rnk = 1;<\/code><\/pre>\n<\/div>\n\u00a0<\/p>\n
\u00a0<\/p>\n
#\u00a0<\/span>Conclusion<\/h2>\n\u00a0
The six ideas we\u2019ve lined generally seem in SQL coding interview questions. Take note of them, follow interview questions that contain these ideas, be taught the right approaches, and also you\u2019ll considerably enhance your probabilities in your interviews.
\u00a0
\u00a0<\/p>\n
Nate Rosidi<\/a><\/strong><\/b><\/a> is an information scientist and in product technique. He is additionally an adjunct professor educating analytics, and is the founding father of StrataScratch, a platform serving to information scientists put together for his or her interviews with actual interview questions from high firms. Nate writes on the most recent traits within the profession market, provides interview recommendation, shares information science initiatives, and covers every little thing SQL.<\/p>\n<\/p><\/div>\n<\/script>
\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Picture by writer | Canva \u00a0 An interviewer’s job is to search out essentially the most appropriate candidates for the marketed place. In doing so, they are going to gladly arrange SQL interview inquiries to see if they’ll catch you off guard. There are a number of SQL ideas at which candidates typically fail. Hopefully, […]<\/p>\n","protected":false},"author":2,"featured_media":6410,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[55],"tags":[5210,1893,157,5211,451,1808],"class_list":["post-6408","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-machine-learning","tag-candidates","tag-concepts","tag-data","tag-fail","tag-interviews","tag-sql"],"_links":{"self":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/6408","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6408"}],"version-history":[{"count":1,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/6408\/revisions"}],"predecessor-version":[{"id":6409,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/6408\/revisions\/6409"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/media\/6410"}],"wp:attachment":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6408"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Why It\u2019s Laborious: Candidates memorize what every window perform<\/a><\/strong> does however don\u2019t actually perceive how window frames, partitions, or ordering truly work.<\/p>\n
Widespread Errors: A standard mistake shouldn’t be specifying Instance: In this instance,<\/a> you might want to discover customers who made a second buy inside 7 days of any earlier buy.<\/p>\n You may write this question.<\/p>\n \u00a0<\/p>\n At first look, every little thing might sound proper. The code even outputs one thing that may seem like an accurate reply.<\/p>\n \u00a0 To begin with, we’re fortunate that the code works in any respect! This occurs just because I\u2019m writing it in PostgreSQL<\/a><\/strong>. In another SQL flavors, you\u2019d get an error since Second, the output is unsuitable; I highlighted some rows that shouldn\u2019t be there. Why do they seem, then?<\/p>\n They seem as a result of we didn\u2019t specify an This isn’t what the query asks. We have to evaluate every transaction to the earlier one by date. In different phrases, we have to specify this explicitly within the \u00a0<\/p>\n \u00a0 Widespread Mistake: Making an attempt to make use of combination capabilities in Instance: This interview query<\/a> asks you to search out the whole income made by every vineyard. Solely wineries the place 90 is the bottom variety of factors for any of their varieties needs to be thought of.<\/p>\n Many will see this as a simple query and unexpectedly write this question.<\/p>\n \u00a0<\/p>\n Nonetheless, that code will throw an error stating that combination capabilities will not be allowed within the \u00a0<\/p>\n \u00a0 Widespread Mistake: Utilizing subqueries and complicating the question when becoming a member of a desk with itself can be less complicated and sooner, particularly when filtering by dates or occasions.<\/p>\n Instance: Right here\u2019s a query<\/a> asking you to point out the change of each foreign money\u2019s change price between 1 January 2020 and 1 July 2020.<\/p>\n You may resolve this by writing an outer correlated subquery that fetches the July 1 change charges, then subtracts the January 1 change charges, which come from the inside subquery.<\/p>\n \u00a0<\/p>\n This returns an accurate output, however such an answer is unnecessarily sophisticated. A a lot less complicated answer, with fewer strains of code, includes self-joining a desk with itself after which making use of two date filtering situations within the \u00a0<\/p>\n \u00a0 Widespread Mistake: Utilizing deeply nested Instance: Within the interview query<\/a> from Google and Netflix, you might want to discover the highest actors based mostly on their common film score throughout the style through which they seem most ceaselessly.<\/p>\n The answer utilizing CTEs is as follows.<\/p>\n \u00a0<\/p>\n It’s comparatively sophisticated, but it surely nonetheless consists of six clear CTEs, with the code’s readability enhanced by clear aliases.<\/p>\n Curious what the identical answer would appear to be utilizing solely subqueries? Right here it’s.<\/p>\n \u00a0<\/p>\n There’s redundant logic repeated throughout subqueries. What number of subqueries is that? I do not know. The code is unimaginable to keep up. Though I simply wrote it, I\u2019d nonetheless want half a day to grasp it if I needed to alter one thing tomorrow. Moreover, the utterly meaningless subquery aliases don’t assist.<\/p>\n \u00a0<\/p>\n \u00a0 Widespread Mistake: Utilizing Instance: There\u2019s an interview query by IBM<\/a> that asks you to calculate the whole variety of interactions and the whole variety of contents created for every buyer.<\/p>\n It doesn\u2019t sound too difficult, so that you may write this answer with two CTEs, the place one CTE counts the variety of interactions per buyer, whereas the opposite counts the variety of content material objects created by a buyer. Within the remaining \u00a0<\/p>\n Virtually proper. Right here\u2019s the output. (By the best way, you see double citation marks (“”) as an alternative of \u00a0 The highlighted rows comprise What we\u2019re lacking within the above answer is \u00a0<\/p>\n \u00a0 Widespread Mistake: Utilizing Instance: This query<\/a> asks you to establish the best-selling merchandise for every month, and there\u2019s no have to separate months by 12 months. One of the best-selling merchandise is calculated as The naive strategy can be this. First, extract the sale month from \u00a0<\/p>\n As I stated, that is naive; the output considerably resembles what we’d like, however we’d like this for each month, not only one.<\/p>\n \u00a0 One of many appropriate approaches is to make use of the Then, in the principle question, we merely choose the required columns and output solely rows the place the rank is 1 utilizing the \u00a0<\/p>\n \u00a0<\/p>\n \u00a0 Nate Rosidi<\/a><\/strong><\/b><\/a> is an information scientist and in product technique. He is additionally an adjunct professor educating analytics, and is the founding father of StrataScratch, a platform serving to information scientists put together for his or her interviews with actual interview questions from high firms. Nate writes on the most recent traits within the profession market, provides interview recommendation, shares information science initiatives, and covers every little thing SQL.<\/p>\n<\/p><\/div>\n <\/script> Picture by writer | Canva \u00a0 An interviewer’s job is to search out essentially the most appropriate candidates for the marketed place. In doing so, they are going to gladly arrange SQL interview inquiries to see if they’ll catch you off guard. There are a number of SQL ideas at which candidates typically fail. Hopefully, […]<\/p>\n","protected":false},"author":2,"featured_media":6410,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[55],"tags":[5210,1893,157,5211,451,1808],"class_list":["post-6408","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-machine-learning","tag-candidates","tag-concepts","tag-data","tag-fail","tag-interviews","tag-sql"],"_links":{"self":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/6408","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6408"}],"version-history":[{"count":1,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/6408\/revisions"}],"predecessor-version":[{"id":6409,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/6408\/revisions\/6409"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/media\/6410"}],"wp:attachment":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6408"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}ORDER BY<\/code> in rating window capabilities or worth window capabilities, similar to LEAD()<\/code> or LAG()<\/code><\/a><\/strong>, and anticipating the question to work or for the end result to be deterministic.<\/p>\nWITH ordered_tx AS (
\n\u00a0 SELECT user_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0created_at::date AS tx_date,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0LAG(created_at::DATE) OVER (PARTITION BY user_id) AS prev_tx_date
\n\u00a0 FROM amazon_transactions
\n)
\n
\nSELECT DISTINCT user_id
\nFROM ordered_tx
\nWHERE prev_tx_date IS NOT NULL AND tx_date - prev_tx_date <= 7;<\/code><\/pre>\n<\/div>\n
\u00a0<\/p>\nORDER BY<\/code> is necessary in rating and analytical window capabilities.<\/p>\nORDER BY<\/code> clause within the LAG()<\/code> window perform. With out it, the row order is bigoted. So, we’re evaluating the present transaction to some random earlier row for that person, not the one which occurred instantly earlier than it in time.<\/p>\nORDER BY<\/code> clause throughout the LAG()<\/code> perform.<\/p>\nWITH ordered_tx AS (
\n\u00a0 SELECT user_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0created_at::date AS tx_date,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0LAG(created_at::DATE) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_tx_date
\n\u00a0 FROM amazon_transactions
\n)
\n
\nSELECT DISTINCT user_id
\nFROM ordered_tx
\nWHERE prev_tx_date IS NOT NULL AND tx_date - prev_tx_date <= 7;<\/code><\/pre>\n<\/div>\n#\u00a0<\/span>2. Filtering With Aggregates (Particularly HAVING vs. WHERE)<\/h2>\n
Why It\u2019s Laborious: Folks typically don\u2019t perceive the execution order in SQL, which is: FROM<\/code> -> WHERE<\/code> -> GROUP BY<\/code> -> HAVING<\/code> -> SELECT<\/code> -> ORDER BY<\/code>. This order implies that WHERE<\/code> filters rows earlier than aggregation, and HAVING<\/code> filters after<\/a><\/strong>. That additionally, logically, means that you would be able to\u2019t use combination capabilities within the WHERE<\/code> clause.<\/p>\nWHERE<\/code> in a grouped question and getting an error.<\/p>\nSELECT vineyard,
\n\u00a0 \u00a0 \u00a0 \u00a0selection,
\n\u00a0 \u00a0 \u00a0 \u00a0SUM(worth) AS total_revenue
\nFROM winemag_p1
\nWHERE MIN(factors) >= 90
\nGROUP BY vineyard, selection
\nORDER BY vineyard, total_revenue DESC;<\/code><\/pre>\n<\/div>\nWHERE<\/code> clause. This beautiful a lot explains every little thing. The answer? Transfer the filtering situation from WHERE<\/code> to HAVING<\/code>.<\/p>\nSELECT vineyard,
\n\u00a0 \u00a0 \u00a0 \u00a0selection,
\n\u00a0 \u00a0 \u00a0 \u00a0SUM(worth) AS total_revenue
\nFROM winemag_p1
\nGROUP BY vineyard, selection
\nHAVING MIN(factors) >= 90
\nORDER BY vineyard, total_revenue DESC;<\/code><\/pre>\n<\/div>\n#\u00a0<\/span>3. Self-Joins for Time-Based mostly or Occasion-Based mostly Comparisons<\/h2>\n
Why It\u2019s Laborious: The thought of becoming a member of a desk with itself<\/a><\/strong> is sort of unintuitive, so candidates typically neglect it\u2019s an choice.<\/p>\nSELECT jan_rates.source_currency,
\n\u00a0 (SELECT exchange_rate\u00a0
\n\u00a0 \u00a0FROM sf_exchange_rate\u00a0
\n\u00a0 \u00a0WHERE source_currency = jan_rates.source_currency AND date=\"2020-07-01\") - jan_rates.exchange_rate AS distinction
\nFROM (SELECT source_currency, exchange_rate
\n\u00a0 \u00a0 \u00a0 FROM sf_exchange_rate
\n\u00a0 \u00a0 \u00a0 WHERE date=\"2020-01-01\"
\n) AS jan_rates;<\/code><\/pre>\n<\/div>\nWHERE<\/code> clause.<\/p>\nSELECT jan.source_currency,
\n\u00a0 \u00a0 \u00a0 \u00a0jul.exchange_rate - jan.exchange_rate AS distinction
\nFROM sf_exchange_rate jan
\nJOIN sf_exchange_rate jul ON jan.source_currency = jul.source_currency
\nWHERE jan.date=\"2020-01-01\" AND jul.date=\"2020-07-01\";<\/code><\/pre>\n<\/div>\n#\u00a0<\/span>4. Subqueries vs. Widespread Desk Expressions (CTEs)<\/h2>\n
Why It\u2019s Laborious: Folks typically get caught on subqueries as a result of they be taught them earlier than Widespread Desk Expressions (CTEs) and proceed utilizing them for any question with layered logic. Nonetheless, subqueries can get messy in a short time.<\/p>\nSELECT<\/code> statements when CTEs can be a lot less complicated.<\/p>\nWITH genre_stats AS
\n\u00a0 (SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0FROM top_actors_rating
\n\u00a0 \u00a0GROUP BY actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 style),
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0
\nmax_genre_count AS
\n\u00a0 (SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 MAX(movie_count) AS max_count
\n\u00a0 \u00a0FROM genre_stats
\n\u00a0 \u00a0GROUP BY actor_name),
\n\u00a0 \u00a0 \u00a0
\ntop_genres AS
\n\u00a0 (SELECT gs.*
\n\u00a0 \u00a0FROM genre_stats gs
\n\u00a0 \u00a0JOIN max_genre_count mgc ON gs.actor_name = mgc.actor_name
\n\u00a0 \u00a0AND gs.movie_count = mgc.max_count),
\n\u00a0 \u00a0 \u00a0
\ntop_genre_avg AS
\n\u00a0 (SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 MAX(avg_rating) AS max_avg_rating
\n\u00a0 \u00a0FROM top_genres
\n\u00a0 \u00a0GROUP BY actor_name),
\n\u00a0 \u00a0
\nfiltered_top_genres AS
\n\u00a0 (SELECT tg.*
\n\u00a0 \u00a0FROM top_genres tg
\n\u00a0 \u00a0JOIN top_genre_avg tga ON tg.actor_name = tga.actor_name
\n\u00a0 \u00a0AND tg.avg_rating = tga.max_avg_rating),
\n\u00a0 \u00a0 \u00a0ranked_actors AS
\n\u00a0 (SELECT *,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 DENSE_RANK() OVER (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0ORDER BY avg_rating DESC) AS rank
\n\u00a0 \u00a0FROM filtered_top_genres),
\n\u00a0 \u00a0
\nfinal_selection AS
\n\u00a0 (SELECT MAX(rank) AS max_rank
\n\u00a0 \u00a0FROM ranked_actors
\n\u00a0 \u00a0WHERE rank <= 3)
\n\u00a0 \u00a0
\nSELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0avg_rating
\nFROM ranked_actors
\nWHERE rank <=
\n\u00a0 \u00a0 (SELECT max_rank
\n\u00a0 \u00a0 \u00a0FROM final_selection);
\n<\/code><\/pre>\n<\/div>\nSELECT ra.actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0ra.style,
\n\u00a0 \u00a0 \u00a0 \u00a0ra.avg_rating
\nFROM (
\n\u00a0 \u00a0 SELECT *,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0DENSE_RANK() OVER (ORDER BY avg_rating DESC) AS rank
\n\u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 SELECT tg.*
\n\u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT gs.*
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS gs
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(movie_count) AS max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS genre_stats
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS mgc
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ON gs.actor_name = mgc.actor_name AND gs.movie_count = mgc.max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 ) AS tg
\n\u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(avg_rating) AS max_avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT gs.*
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS gs
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(movie_count) AS max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS genre_stats
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS mgc
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ON gs.actor_name = mgc.actor_name AND gs.movie_count = mgc.max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS top_genres
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 ) AS tga
\n\u00a0 \u00a0 \u00a0 \u00a0 ON tg.actor_name = tga.actor_name AND tg.avg_rating = tga.max_avg_rating
\n\u00a0 \u00a0 ) AS filtered_top_genres
\n) AS ra
\nWHERE ra.rank <= (
\n\u00a0 \u00a0 SELECT MAX(rank)
\n\u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 SELECT *,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0DENSE_RANK() OVER (ORDER BY avg_rating DESC) AS rank
\n\u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT tg.*
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT gs.*
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS gs
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(movie_count) AS max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS genre_stats
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS mgc
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ON gs.actor_name = mgc.actor_name AND gs.movie_count = mgc.max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS tg
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(avg_rating) AS max_avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT gs.*
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS gs
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(movie_count) AS max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM (
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT actor_name,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0style,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0COUNT(*) AS movie_count,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0AVG(movie_rating) AS avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM top_actors_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name, style
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS genre_stats
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS mgc
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ON gs.actor_name = mgc.actor_name AND gs.movie_count = mgc.max_count
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS top_genres
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 GROUP BY actor_name
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ) AS tga
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ON tg.actor_name = tga.actor_name AND tg.avg_rating = tga.max_avg_rating
\n\u00a0 \u00a0 \u00a0 \u00a0 ) AS filtered_top_genres
\n\u00a0 \u00a0 ) AS ranked_actors
\n\u00a0 \u00a0 WHERE rank <= 3
\n);<\/code><\/pre>\n<\/div>\n#\u00a0<\/span>5. Dealing with NULLs in Logic<\/h2>\n
Why It\u2019s Laborious: Candidates typically suppose that NULL<\/code> is the same as one thing. It\u2019s not. NULL<\/code> isn\u2019t equal to something \u2014 not even itself. Logic involving NULL<\/code>s behaves in another way from logic involving precise values.<\/p>\n= NULL<\/code> as an alternative of IS NULL<\/code> in filtering or lacking output rows as a result of NULL<\/code>s break the situation logic.<\/p>\nSELECT<\/code>, you FULL OUTER JOIN<\/code> the 2 CTEs, and you’ve got the answer. Proper?<\/p>\nWITH interactions_summary AS
\n\u00a0 (SELECT customer_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 COUNT(*) AS total_interactions
\n\u00a0 \u00a0FROM customer_interactions
\n\u00a0 \u00a0GROUP BY customer_id),
\n\u00a0 \u00a0
\ncontent_summary AS
\n\u00a0 (SELECT customer_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 COUNT(*) AS total_content_items
\n\u00a0 \u00a0FROM user_content
\n\u00a0 \u00a0GROUP BY customer_id)
\n\u00a0 \u00a0
\nSELECT i.customer_id,
\n\u00a0 i.total_interactions,
\n\u00a0 c.total_content_items
\nFROM interactions_summary AS i
\nFULL OUTER JOIN content_summary AS c ON i.customer_id = c.customer_id
\nORDER BY customer_id;<\/code><\/pre>\n<\/div>\nNULL<\/code>. That\u2019s how the StrataScratch UI shows it, however belief me, the engine nonetheless treats them for what they’re: NULL<\/code> values).<\/p>\n
\u00a0<\/p>\nNULL<\/code>s. This makes the output incorrect. A NULL<\/code> worth is neither the shopper ID nor the variety of interactions and contents, which the query explicitly asks you to point out.<\/p>\nCOALESCE()<\/code><\/a><\/strong> to deal with NULL<\/code>s within the remaining SELECT<\/code>. Now, all the purchasers with out interactions will get their IDs from the content_summary<\/code> CTE. Additionally, for purchasers that don\u2019t have interactions, or content material, or each, we\u2019ll now exchange NULL<\/code> with 0, which is a sound quantity.<\/p>\nWITH interactions_summary AS
\n\u00a0 (SELECT customer_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 COUNT(*) AS total_interactions
\n\u00a0 \u00a0FROM customer_interactions
\n\u00a0 \u00a0GROUP BY customer_id),
\n\u00a0 \u00a0
\ncontent_summary AS
\n\u00a0 (SELECT customer_id,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 COUNT(*) AS total_content_items
\n\u00a0 \u00a0FROM user_content
\n\u00a0 \u00a0GROUP BY customer_id)
\n\u00a0 \u00a0
\nSELECT COALESCE(i.customer_id, c.customer_id) AS customer_id,
\n\u00a0 \u00a0 \u00a0 \u00a0COALESCE(i.total_interactions, 0) AS total_interactions,
\n\u00a0 \u00a0 \u00a0 \u00a0COALESCE(c.total_content_items, 0) AS total_content_items
\nFROM interactions_summary AS i
\nFULL OUTER JOIN content_summary AS c ON i.customer_id = c.customer_id
\nORDER BY customer_id;<\/code><\/pre>\n<\/div>\n#\u00a0<\/span>6. Group-Based mostly Deduplication<\/h2>\n
Why It\u2019s Laborious: Group-based deduplication means you\u2019re choosing one row per group, e.g., \u201cmost up-to-date\u201d, \u201chighest rating\u201d, and so on. At first, it sounds such as you solely want to choose one row per person. However you may\u2019t use GROUP BY<\/code> except you combination. Alternatively, you typically want a full row, not a single worth that aggregation and GROUP BY<\/code> return.<\/p>\nGROUP BY<\/code> + LIMIT 1<\/code> (or DISTINCT ON<\/a><\/strong>, which is PostgreSQL-specific) as an alternative of ROW_NUMBER()<\/code> or RANK()<\/code>, the latter in order for you ties included.<\/p>\nunitprice * amount<\/code>.<\/p>\ninvoicedate<\/code>, choose description<\/code>, and discover the whole gross sales by summing unitprice * amount<\/code>. Then, to get the whole gross sales by month and product description, we merely GROUP BY<\/code> these two columns. Lastly, we solely want to make use of ORDER BY<\/code> to kind the output from the very best to the worst-selling product and use LIMIT 1<\/code> to output solely the primary row, i.e., the best-selling merchandise.<\/p>\nSELECT DATE_PART('MONTH', invoicedate) AS sale_month,
\n\u00a0 \u00a0 \u00a0 \u00a0description,
\n\u00a0 \u00a0 \u00a0 \u00a0SUM(unitprice * amount) AS total_paid
\nFROM online_retail
\nGROUP BY sale_month, description
\nORDER BY total_paid DESC
\nLIMIT 1;<\/code><\/pre>\n<\/div>\n
\u00a0<\/p>\nRANK()<\/code> window perform. With this strategy, we observe an analogous technique to the earlier code. The distinction is that the question now turns into a subquery within the FROM<\/code> clause. As well as, we use RANK()<\/code> to partition the information by month after which rank the rows inside every partition (i.e., for every month individually) from the best-selling to the worst-selling merchandise.<\/p>\nWHERE<\/code> clause.<\/p>\nSELECT month,
\n\u00a0 \u00a0 \u00a0 \u00a0description,
\n\u00a0 \u00a0 \u00a0 \u00a0total_paid
\nFROM
\n\u00a0 (SELECT DATE_PART('month', invoicedate) AS month,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 description,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SUM(unitprice * amount) AS total_paid,
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 RANK() OVER (PARTITION BY DATE_PART('month', invoicedate) ORDER BY SUM(unitprice * amount) DESC) AS rnk
\n\u00a0 \u00a0FROM online_retail
\n\u00a0 \u00a0GROUP BY month, description) AS tmp
\nWHERE rnk = 1;<\/code><\/pre>\n<\/div>\n#\u00a0<\/span>Conclusion<\/h2>\n
The six ideas we\u2019ve lined generally seem in SQL coding interview questions. Take note of them, follow interview questions that contain these ideas, be taught the right approaches, and also you\u2019ll considerably enhance your probabilities in your interviews.
\u00a0
\u00a0<\/p>\n
\n
<\/p>\n","protected":false},"excerpt":{"rendered":"