Adrian Medina’s Post

Week 3: The French Press of Subqueries Greetings, coffee and tea connoisseurs! ☕️🍵 Today’s advanced tip is about **Subqueries** – they’re like a French press, brewing rich results with a bit of extra effort. 🔷Tip: Use subqueries to break down complex queries, but watch out for performance hits – just like over-extracting coffee grounds. ✨Example: You need the names of customers who’ve ordered more than once. Let’s break it down: ```sql SELECT customer_name FROM Customers WHERE customer_id IN ( SELECT customer_id FROM Orders GROUP BY customer_id HAVING COUNT(*) > 1 ); ``` This nested subquery gets the job done, but can be slow – like waiting for your French press to brew. 🫰🏻Performance Boost: Consider joining directly with aggregated data: ```sql SELECT c.customer_name FROM Customers c JOIN ( SELECT customer_id FROM Orders GROUP BY customer_id HAVING COUNT(*) > 1 ) o ON c.customer_id = o.customer_id; ``` Now you’re brewing efficiency and flavor together! Share your ideas to improve subqueries' performance and stay tuned for more brewed tips next week! Till then, Happy querying! ☕️💡 #SQL #DataAnalytics #CoffeeLovers #TeaLovers #AdvancedSQL

Part of my work is strategically pricing items in #SQLServer for our restaurant clientele. Depending on the client, there are rules about price endings. So, if I am updating pricing by a certain magnitude, I want to avoid pricing items that would violate. Now, the client I was working on usually uses .X9 endings for most of their menu. Usually, running an UPDATE statement with no violators is as simple as filtering WHERE price NOT LIKE '%.09', for example. However, the menu I was pricing had more variable endings (i.e., .05, .09, .04, .00). In this case, I can't just use the usual filter and hope to catch all the different endings. So, how do I? There are a few ways I can think of to capture what I need: 1) use SUBSTRING. It can help filter on the tenths decimal place. The challenge is that it only works with string values, and price is money. By tweaking SUBSTRING and making it SUBSTRING(RIGHT(price, 2), 1, 1), I can allow price to fit the criteria. In the example above, if price is 9.09 or 9.05, I'm telling SQL to start to the right of the value and count 2 places = 09 or 05. Then, SUBSTRING takes that 09 or 05, starts at position 1, and only returns 1 position = 0. Every price that is within .0X will be captured. 2) use WHERE price NOT LIKE '%.0_' and use the wildcard's _ to find any ending that ends with .0X. If I have to filter on many endings though, I would have to use multiple AND operators to capture every tenths decimal. 3) Take a LEFT of the RIGHT. As in, LEFT(RIGHT(price, 2), 1). Funny stuff. There may be other ways, but these examples show just how diverse #problemsolving with #SQL can be. Here's a look at each method below.

🚀 Exciting News! 🚀 Thrilled to share our latest GitHub project! 🎉 We've analyzed fast food sales using MySQL 🍔📊 Uncover key insights for better business decisions 💡 Explore now: https://lnkd.in/e3btaTmW #FastFood #MySQL #DataAnalysis #BusinessInsights

Hello everyone, hope you're all doing well! ⚡ Get ready for some Index magic in SQL! 🪄 Imagine you're at a bustling farmer's market, searching for your favorite fruits and veggies. Without indexes, it's like wandering through every stall, hoping to stumble upon what you need. But with indexes, it's as if the market has signs pointing to each type of produce, making it a breeze to find exactly what you're craving! 🍎🥕✨ Meet the Index Heroes: 🔹 Clustered Index: It's like having all the fruits arranged alphabetically by type. So, finding your favorite apples or oranges becomes a snap! 🔹 Non-clustered Index: Picture having separate signs for different categories like vegetables, berries, or tropical fruits. Now you can head straight to the section you're interested in! 🔹 Unique Index: Think of it as each fruit having its own unique sticker or label. No two fruits have the same sticker, ensuring that every item is easily identifiable. With indexes leading the way, navigating the market of data becomes a delightful adventure, ensuring you always find exactly what you're looking for! 🍇🍐🔍 🔒 Let's journey into the world of data security with Transactions in SQL! 🌟 Imagine you're baking a cake, and you need to buy ingredients from the store. Transactions are like putting all your ingredients in your basket before heading to the checkout. You either get everything you need or nothing at all! Here's how it works: 🔹 BEGIN TRANSACTION: It's like starting your shopping trip. You're ready to gather all your ingredients. 🔹 COMMIT TRANSACTION: Imagine you successfully find everything on your list and pay at the checkout. Your mission is accomplished, and you can enjoy baking your cake! 🔹 ROLLBACK TRANSACTION: But what if you realize you forgot your wallet? No worries! With rollback, it's like putting everything back on the shelves and starting over. Your list stays intact, and you can try again without any mishaps. So, whether you're baking a cake or managing data, Transactions ensure that everything stays consistent and error-free from start to finish! 🍰🔐 #SQL #INDEX #TRANSACTION #DATAANALYSIS

Question: In your kitchen, do you organize your pots and pans by size or stack them by recency like a maniac? Your database should do the same thing! Organize data at ingestion so your queries are fast and efficient. ClickHouse’s MergeTree is one great example.

Data with Danny SQL Case Study 1: Danny's Diner Grateful to Danny Ma for sharing such an engaging dataset that made this analysis an enjoyable journey. A special shoutout to Suresh Krishna for his detailed SQL project session, which helped me build the skills needed for this project. 𝟖 𝐖𝐞𝐞𝐤 𝐒𝐐𝐋 𝐂𝐡𝐚𝐥𝐥𝐞𝐧𝐠𝐞 by Danny Ma. 👉 GitHub Repository Link: https://lnkd.in/gcwUEV4Z #data #sql #casestudies #mysql #share #dataanlytics #8WeekSQLChallenge #BusinessInsights #ProblemSolving

Jay Jagannath 🙏🙏 Today let's take a deep dive into STORED PROCEDURE in Sequel. Imagine you're baking cookies and you have a recipe written down. This recipe tells you exactly what ingredients to use and what steps to follow to make delicious cookies. In SQL, a stored procedure is like having a recipe stored in your kitchen. For example, let's say you want to calculate the total price of items in your shopping cart. Instead of writing the same calculation steps every time you need the total price, you can create a stored procedure called `CalculateTotalPrice`. This stored procedure will have all the necessary SQL commands to add up the prices of the items in your cart. Now, whenever you want to find the total price, you don't need to write the calculation steps again. You simply call the `CalculateTotalPrice` stored procedure, just like following a recipe, and it gives you the total price instantly. Stored procedures are like pre-written recipes that save you time and effort. They make your SQL tasks easier by letting you reuse common actions without having to write them over and over again. Here's an example of a stored procedure in SQL for calculating the total price of items in a shopping cart: create procedure CalculateTotalPrice as begin -- declare variables declare @TotalPrice decimal(10, 2); -- calculate total price select @TotalPrice = sum(price * quantity) from shopping_cart; -- Return the total price select @TotalPrice as TotalPrice; end; In this example: - We create a stored procedure named CalculateTotalPrice. - Inside the procedure, we declare a variable @TotalPrice of type decimal(10, 2) to store the calculated total price. - The select @TotalPrice = sum(price * quantity) from shopping_cart; line calculates the total price by multiplying the price of each item in the shopping cart by its quantity and then summing them up. - Finally, we return the calculated total price using select @TotalPrice as TotalPrice. To use this stored procedure, you can simply call it like this: -- Calling the stored procedure EXEC CalculateTotalPrice; This will execute the stored procedure and give you the total price of items in the shopping cart. #dataanalyst #businessanalyst #sql #storedprocedure

🚀 Data-Driven Insights for Pizza Restaurant Success! 🍕📊 I recently completed an in-depth analysis of a pizza ordering dataset using SQL, and the results were fascinating! By analyzing order data, pizza types, sizes, and ingredients, I was able to extract key insights that can significantly impact a pizza restaurant’s operations and profitability. SQL code is used to answer each analysis question. The queries utilize techniques such as subqueries, window functions, aggregate functions, JOIN, GROUP BY, and ORDER BY to extract insights from the four datasets. Here are some of the key questions I answered: 1.      Which pizza types generate the most revenue? 2.      What’s the most popular pizza size among customers? 3.      Which ingredients are most commonly used in pizzas? 🧀🍅 4.      How can we identify peak revenue days and optimize inventory & staffing? 5.      What’s the average order value across different dates? And much more! By leveraging these insights, businesses can: - Optimize menu offerings based on customer preferences 📝 - Enhance ingredient management and reduce waste 🌱 - Create targeted promotions and upsell strategies 💡 - Streamline operations to match peak demand periods ⏰ 💡 #Takeaway: Data isn't just numbers—it's the foundation for smart, strategic business decisions. Whether you’re in the food industry or any other field, data can unlock untapped opportunities. #SQL #DataAnalysis #BusinessIntelligence #RestaurantAnalytics #DataDriven #Operations #CustomerInsights #FoodIndustry #DataScience --- Feel free to check out the project on GitHub by clicking the link below for more details! 🍕✨ https://lnkd.in/enB_AUjS Feel free to connect if you'd like to chat more about this project or how data can drive growth in your business! 😊

I solved this SQL query today from StrataScratch. For this query, I had to find the worst restaurant business in San Francisco for the years 2015-2018. The worst businesses are the ones with the most restaurant violations during the year. Solving this query required the following three steps: 🎯 Calculate the number of violations for each business per year. I created a CTE to accomplish this step. I counted the number of violation IDs and grouped the output by inspection year and business year. 🎯 Calculate the rank numbers of the highest business violations for each business per year. I also created a CTE to accomplish this step. I used the RANK window function, partitioned by inspection year, and sorted by violation count. Since I am only interested in the highest business violations, I don't have to use DENSE_RANK to account for any ranking ties. 🎯 Find the business with the highest restaurant violations. I selected the required columns from the rank numbers CTE and filtered the output to rank numbers equal to one. #sql #stratascratch #cte #windowfunction