Additionally, you might try coming up with some questions of your own to see if you can find the solution. If you didn’t get the solutions to these queries on the first pass, don’t be afraid to come back another time and give them another try. Being able to break a problem down into the necessary tables and finding a solution using the resulting table is very useful in practice. Arguably, the advanced features of Subqueries and CTEs are the most widely used in an analytics role within a company. #Recap This lesson was the first of the more advanced sequence in writing SQL. How many accounts had more total purchases than the account name which has bought the most standard_qty paper throughout their lifetime as a customer?įor the customer that spent the most (in total over their lifetime as a customer) total_amt_usd, how many web_events did they have for each channel? Provide the name of the sales_rep in each region with the largest amount of total_amt_usd sales.įor the region with the largest sales total_amt_usd, how many total orders were placed? Try to perform each of the earlier queries again, but using a WITH instead of a subquery. Therefore, you can write any of the queries we worked with in the “Subquery Mania” using a WITH. The quiz at the bottom will assure you are catching all of the necessary components of these new queries.Įssentially a WITH statement performs the same task as a Subquery. You can add more and more tables using the WITH statement in the same way. What was the month/year combo for the first order placed?. If we are returning an entire table, then we must use an ALIAS for the table, and perform additional logic on the entire table. If we returned an entire column IN would need to be used to perform a logical argument. This is because the subquery is treated as an individual value ( or set of values in the IN case) rather than as a table.Īlso, notice the query here compared a single value. On the next concept, we will work through this example, and then you will get some practice on answering some questions on your own.Įxpert Tip Note that you should not include an alias when you write a subquery in a conditional statement. However, if you are only returning a single value, you might use that value in a logical statement like WHERE, HAVING, or even SELECT - the value could be nested within a CASE statement. In the first subquery you wrote, you created a table that you could then query again in the FROM statement. Then you will get some practice tackling some additional problems on your own.įROM (SELECT DATE_TRUNC('day',occurred_at) AS day, In the next couple of concepts, we will walk through an example together. Whenever we need to use existing tables to create a new table that we then want to query again, this is an indication that we will need to use some sort of subquery. If you can’t yet think of a question that might require such a query, don’t worry because you are about to see a whole bunch of them! This is where the queries of this lesson come to the rescue. However, if we were able to create new tables from the existing tables, we know we could query these new tables to answer our question. Sometimes the question you are trying to answer doesn’t have an answer when working directly with existing tables in database. 1.Subqueries 2.Table Expressions 3.Persistent Derived Tablesīoth subqueries and table expressions are methods for being able to write a query that creates a table, and then write a query that interacts with this newly created table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |