Search this site

In today's episode, we're going back to basics. SQL is a language you must not only speak but also comprehend. You can craft a beautiful query, knowing exactly what you need from your data. You can even communicate it, yet somehow all you see is nonsense. Like in any relationship, communication works both ways, so let's ensure you're getting your message across clearly. It is important to know that there is an order in which SQL will execute your query. This is how it rolls: In this awesome graphic above, which I made myself, you'll see how SQL executes. First, SQL identifies the table you need to pull from. If you use a JOIN, it will be processed in this crucial step. Tell it what to SELECT. Then, with the WHERE clause, you get to choose your conditions, allowing SQL to filter the rows accordingly. GROUP BY and HAVING are especially helpful when using aggregate functions and applying conditions to those groupings. Only then will SQL select the fields you've chosen, ORDER them BY the column of your choice, and LIMIT the results to the number of rows you specified. The result is a table displaying your queried data. Let's go over an example. I have a database containing two tables: movies and critic_reviews. I wanted to answer the question of: what are the top 10 publications starting with the letter "A", that have the most reviews for movies with audience score above 90%. SELECT TOP 10 COUNT(m.movieTitle) AS number_of_movies, cr.publicationName AS publication_name FROM movies as m JOIN critic_reviews as cr ON m.movieId = cr.movieId WHERE m.audience_score > '90%' GROUP BY cr.publicationName HAVING cr.publicationName LIKE 'A%' ORDER BY number_of_movies DESC ; The way SQL reads it, I imagine, would be something along: I am joining movies and critic_reviews tables. The common column is movieID, so I will match it and take those that have common lines. Then I will take movies that will have audience score above 90% from the column aduaince_score in movies part of the table. From all of those, I will group them by publication name and take only those that starts with the letter "A". I am asked to give count how many movieTitiles were considered by those publications and associate it to filtered before publication name. Ta-da! I hope this gives you an idea how SQL process the query. NOTE! In SSMS to limit you would use SELECT TOP 10 Or how many you need to see.

We're here for data, so let's look at some numbers. In 2023, the total revenue of the recorded music industry amounted to 28.6 billion U.S. dollars. Global streaming revenues reached 19.3 billion U.S. dollars in the same year It means that over 67% of all music revenue comes from streaming services. Have you ever wondered what lies beyond Spotify, Pandora, and iTunes? A quick search will reveal that there are currently over 200 streaming services globally. For artists, managing this landscape can be quite a headache. Enter a digital music distribution company specializing in revenue management, potentially offering a solution. Data analysis can determine the most beneficial outcomes for both the company and the artist. When objectives aren't clearly defined, creativity becomes crucial. Data analysis itself is an art. One question leads to another. With this database, it's easy to become lost in endless analysis. However, the crucial task is evaluating which data section provides the most revenue insights. I've prepared this exploratory data analysis to address the most critical business question for the music distribution company: Which music generates the most revenue? All the below screenshots of analysis, can be found on my GitHub Page EXPLORATORY DATA ANALYSIS Step 1. Examining if data types in each column are appropriate and consistent with the data they store. In order to make it look this neat, I needed to Upload csv files to SSMS. Uniform all field names and check their data types. Define which fields are my keys and are allow to be nulls. As far as exploratory data analysis goes, the first task can be checked out. I examined the types and made sure they are consistent. I cannot emphasize enough how important this step is. Numerous error messages can drive you nuts. I was there. Just learn from my mistakes. Right after that, we MUST examine each table for null values, especially in crucial columns. Decide whether nulls are allowed and handle them appropriately. Understand the uniqueness of values in key columns like song_id, album_id, and others. Ensure the uniqueness constraints are met. Before you hit the query button, and start pretending you are a hardcore heavy metal movie hacker with a black screen, just make yourself familiar with the table. All steps above make you somewhat memorize what each table consists of. It should be logical, of course. But you are the parent here. You make changes so it fits you. But keep it to the convention, in case someone will want to refer to your work later on! Keep those notes. They will come in handy. Little love, little patience, and magic. All my data is nice and cozy and stored inside my music database. I'm sure we are forming a pretty solid friendship at this point, and I am confident we can make this work. This, and no issues from SSMS. It's a nice confirmation, I'm not gonna lie. EXEC sp_help 'royalty_sales' SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'royalty_sales' ; SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'songs' ; SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'albums' ; SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'artists' Step 2. Statistics! To have an idea of how our numbers work, we need to know where they fall. Distribution of Quantitative Data is the main focus this evening. Exploring each table separately and then joining them will paint a picture quite soon. Finding the average values of crucial columns, checking the maximum and minimum values, and sums is satisfying enough to see if our data actually makes sense. Good, old judgment is important. If the average song makes 34 cents, please check if the maximum value for a song doesn't go to $100. Let's be serious here. Exploring: royalty_sales, albums, artists, songs -- quering dbo.songs for reference EXEC sp_help 'songs' -- checking for duplicates in dbo.songs SELECT song_id, COUNT (*) AS count FROM songs GROUP BY song_id HAVING COUNT(*) > 1; -- no record with this combination shows that each row is unique SELECT song_id, song_title, album_id, COUNT(*) AS count FROM songs GROUP BY song_id, song_title, album_id HAVING COUNT(*) > 1; --example of duplicate SELECT * FROM dbo.songs where song_id = 'SOALSZJ1370F1A7C75' -- let's check for duplicates for albums SELECT album_id, COUNT (*) AS count FROM albums GROUP BY album_id HAVING COUNT(*) > 1; -- being empty - no results - means that I can establish primary key here -- for dbo.artists SELECT artist_id, COUNT (*) AS count FROM artists GROUP BY artist_id HAVING COUNT(*) > 1; --empty again so I can make it a primary key -- making PK in songs table ALTER TABLE songs ADD CONSTRAINT PK_songs_album_id_song_id PRIMARY KEY (album_id, song_id); -- droping album_id from royalties because it can be queried with song_id and it is empty anyways ALTER TABLE royalty_sales DROP COLUMN album_id; --changing in royalties_sale date_sale for just date ALTER TABLE royalty_sales ALTER COLUMN sale_date DATE; -- creating FK ALTER TABLE albums ADD CONSTRAINT FK_albums_artist_id FOREIGN KEY (artist_id) REFERENCES artists (artist_id); --populate album_id in royalties_sales UPDATE rs SET rs.album_id = s.album_id FROM royalty_sales rs INNER JOIN songs s ON rs.song_id = s.song_id WHERE rs.album_id IS NULL; --is it unique? SELECT sale_id, COUNT (*) AS count FROM royalty_sales GROUP BY sale_id HAVING COUNT(*) > 1; -- Alter the column to be NOT NULL ALTER TABLE royalty_sales ALTER COLUMN sale_id [int] NOT NULL; -- Add the primary key constraint ALTER TABLE royalty_sales ADD CONSTRAINT PK_royalty_sales PRIMARY KEY (sale_id); --final check SELECT * FROM royalty_sales WHERE sale_id IS NULL; Investigating the distribution of quantitative data like quantity, payable_amt_due, and royalty_share. Let me show you example, I'm sure you just itch to get to findings. -- exploring royalty_sales table SELECT * FROM royalty_sales ; -- 114,358 SELECT TOP 10 * FROM royalty_sales -- findings: song_id have some null spaces, check for payable_curency consistency, album_id is also null -- next: how many nulls in song_id SELECT sale_id, count(*) as count FROM royalty_sales WHERE song_id IS NULL GROUP BY sale_id; -- the finding of that is 114,358 rows -- I want to know how many song_id we have and I want to sort them alphabetically SELECT song_id, count(*) as count FROM royalty_sales GROUP BY song_id ORDER BY count desc; -- with ASC we have 53,766 song_id with null values (tho it still shows no nulls there). -- the highest amout of song_id is 16 SELECT * FROM royalty_sales WHERE song_id = '' ; -- so those have empty spaces and we have 53 766 of them. -- exploring date SELECT sale_id, sale_date FROM royalty_sales ORDER BY sale_date ; --this way I see that the latest sale is 2022-05-31 and the oldest is from 2000-01-01 -- store: do we have null values there, thich is the most/least popular, how many of them is there SELECT store, count(*) as count FROM royalty_sales GROUP BY store ORDER BY count desc; --96 rows, it would mean we have 96 of them, let's check: SELECT count( DISTINCT store) as store FROM royalty_sales ; -- it is 96 of them --now let's check for the null values there SELECT store FROM royalty_sales WHERE store IS NULL ; -- NO NULL VALUES --exploring currency SELECT payable_currency, count(*) AS count FROM royalty_sales GROUP BY payable_currency ORDER BY count desc; -- 685 desc Nepalese Rupee, 684 asc Falkland Island Pounds -- NOTE payable_amt_due is a amount payed in the currency, usd_payable is the amt that is payed in USD dollars --after conversion -- move on to payable_amt_due to see the statistics SELECT COUNT(*) AS num_rows, ROUND(AVG(payable_amt_due),2) AS avg_payable_amt_due, ROUND(MIN(payable_amt_due),2) AS min_payable_amt_due, ROUND(MAX(payable_amt_due),2) AS max_payable_amt_due, ROUND(SUM(payable_amt_due), 2) AS sum_payable_amt_due, ROUND(STDEV(payable_amt_due),2) AS stdev_payable_amt_due FROM royalty_sales ; -- more info from here SELECT COUNT(*) AS num_rows, ROUND(AVG(usd_payable),2) AS avg_usd_payable, ROUND(MIN(usd_payable),2) AS min_usd_payable, ROUND(MAX(usd_payable),2) AS max_usd_payable, ROUND(SUM(usd_payable), 2) AS sum_usd_payable, ROUND(STDEV(usd_payable),2) AS stdev_usd_payable FROM royalty_sales ; -- move on to quantity to see the statistics SELECT count(*) AS num_rows, ROUND(AVG(quantity),2) AS avg_quantity, ROUND(MIN(quantity),2) AS min_quantity, ROUND(MAX(quantity),2) AS max_quantity, ROUND(SUM(quantity), 2) AS sum_quantity, ROUND(STDEV(quantity),2) AS stdev_quantity FROM royalty_sales ; Step 3. Findings. Documenting your findings in some type of documentation will help you organize your information. After a while, you might get confused about what is what. Good doc document or Excel table would suffice for now. What I've learned from this project was to be vigilant of the numbers we work on. They can get tricky, also simple statistics hides bunch of important information about data. Because of that, I can get creative. I can ask more questions. Well... in this case... Funnily enough, this exemplary database wasn't diverse enough to be plotted anywhere, but it was a great SQL exercise. Power BI is a strong tool to graph and present your findings to a non-technical audience. This is another form of artistry because that math tells you everything you need to know. Now, do you know how to show it to someone who is not interested in this whole funny code?