Common Table Expressions (CTEs) in SQL: SQL 30 Day Challenge Day 19

Learn how to create temporary tables that can limit the use of SQL subqueries (and make your life easier) This is just an intro feel free to practice more CTEs online (they are very important)
Questions:
/* Use a CTE to calculate the average track length
per album */
with tracklength as (
select AlbumId, AVG(Milliseconds) as AvgTrackLength
from tracks
group by AlbumId
)
select al.Title, tracklength.AvgTrackLength
from albums al
join tracklength
on al.AlbumId=tracklength.AlbumId;
/* Use two CTEs to get total sales
for each USA customer */
WITH TotalSales AS (
SELECT CustomerId, SUM(Total) AS TotalAmount
FROM Invoices
GROUP BY CustomerId
), TopCustomers AS (
SELECT CustomerId, FirstName, LastName
FROM Customers
WHERE Country = 'USA'
)
SELECT TopCustomers.FirstName, TopCustomers.LastName, TotalSales.TotalAmount
FROM TopCustomers
JOIN TotalSales ON TopCustomers.CustomerId = TotalSales.CustomerId
ORDER BY TotalSales.TotalAmount DESC;
-----------------------------------------------------------------
Download SQLite Studio Here: sqlitestudio.pl/
Download the Chinook Database: www.sqlitetutorial.net/sqlite...
Link to Topic List: docs.google.com/document/d/1f...
Link to Facebook Support Group: / 767988395118964
Link to my Data Etsy Shop (support me to keep making content): jellysgeekygoods.etsy.com
Buy me a coffee to show support: buymeacoffee.com/learningwith...
#sql #sqltutorial #sqlforbeginners

[@malcorub]

Yup, I'm on team CTE as I dislike too many subqueries, they hurt my brain.
One question I 've gotten alot during interviews over the years is WHAT'S THE DIFFERENCE BETWEEN SUBQUERY AND CTE? and also WHAT'S THE DIFFERENCE BETWEEN CTE and TEMPORARY TABLE?

[@learningwithjelly]

CTEs last for the one use so it only applies to the query that is right after the CTE. A temporary table can be created and called on for many times after you created and is stored in your local memory for a given amount of time (at least for the entire SQL session)

[@developbit]

is it possible to provide some more practice for this video? I am still not clear on the CTE in SQL?

[@learningwithjelly]

It is day 20 :)