Sushant Nayak

Spotify SQL Interview Question – can you solve it? Assume there are three Spotify tables: artists, songs, and global_song_rank, which contain information about the artists, songs, and music charts, respectively. Write a query to find the top 5 artists whose songs appear most frequently in the Top 10 of the global_song_rank table. Display the top 5 artist names in ascending order, along with their song appearance ranking. Link to the full problem, multiple hints, and a platform so you can run your SQL query directly in the browser 👇 https://lnkd.in/eV3Rme8S Expected Output: 𝐚𝐫𝐭𝐢𝐬𝐭_𝐧𝐚𝐦𝐞 | 𝐚𝐫𝐭𝐢𝐬𝐭_𝐫𝐚𝐧𝐤 Taylor Swift | 1 Bad Bunny | 2 Drake | 2 Ed Sheeran | 3 Adele | 3 Lady Gaga | 4 Katy Perry | 5 FYI: If two or more artists have the same number of song appearances, they should be assigned the same ranking, and the rank numbers should be continuous (i.e. 1, 2, 2, 3, 4, 5). If you've never seen a rank order like this before, do the rank window function tutorial: https://lnkd.in/eqhd8GSr Comment your solution... curious to see what y'all came up with!

11

1 Comment

Spotify SQL Interview Question – can you solve it? Assume there are three Spotify tables: artists, songs, and global_song_rank, which contain information about the artists, songs, and music charts, respectively. Write a query to find the top 5 artists whose songs appear most frequently in the Top 10 of the global_song_rank table. Display the top 5 artist names in ascending order, along with their song appearance ranking. Link to the full problem, multiple hints, and a platform so you can run your SQL query directly in the browser 👇 https://lnkd.in/eV3Rme8S Expected Output: 𝐚𝐫𝐭𝐢𝐬𝐭_𝐧𝐚𝐦𝐞 | 𝐚𝐫𝐭𝐢𝐬𝐭_𝐫𝐚𝐧𝐤 Taylor Swift | 1 Bad Bunny | 2 Drake | 2 Ed Sheeran | 3 Adele | 3 Lady Gaga | 4 Katy Perry | 5 FYI: If two or more artists have the same number of song appearances, they should be assigned the same ranking, and the rank numbers should be continuous (i.e. 1, 2, 2, 3, 4, 5). If you've never seen a rank order like this before, do the rank window function tutorial: https://lnkd.in/eqhd8GSr Comment your solution... curious to see what y'all came up with!

7

1 Comment

XP 2001 - Reboot >> delete .sys >> restart - we are in 2024 Don’t know how long this silver bullet will exist. But it's an Operation support Engineer's nightmare. If a Tech Architect never considered that a Windows system would ever fail in 2024; then it's a bus-tech nightmare. We all going through that, as a user, customer and as a solution provider. #engineer #crowdstrike

6

Deepak Mahto: Simplify PostgreSQL Execution Plan Analysis with StatsViaExplainAnalyze. 🚀 PostgreSQL Geeks, Have You Heard of StatsViaExplainAnalyze? 🚀 Tuning SQL. It's like the unending quest for the perfect cup of coffee 🚴‍♂️. But fear not, Deepak Mahto's got us covered with something spectacular. 👉 Introducing StatsViaExplainAnalyze! 👈 You might be thinking, "Ugh, more SQL plans?" But hold on. This isn't just any tool; it's the Swiss army knife for PostgreSQL execution plan analysis. 🔪 Imagine having your execution plans, table stats, index stats, and even column stats all neatly packaged in an HTML report. One click, and boom – it's all there. Your rabbit hole just got a bit shorter. 🔹 Why you need it: - Combines execution plans and object stats. - Shares insights effortlessly with your team. - Reduces back-and-forths for additional info. Check out the results: 📄 [Sample Report 1](https://lnkd.in/e4XP9HsM) 📄 [Sample Report 2](https://lnkd.in/eCRbekE9) 📄 [Sample Report 3](https://lnkd.in/e8hBzGU7) Deepak Mahto has brought some Oracle magic to the PostgreSQL world 🌟. Hats off to him! 🎩 Ready to simplify your SQL tuning journey? Dive into it here 📥: [GitHub Repo](https://lnkd.in/eRicWBag) Optimize your queries. Reclaim your weekends. 🌴 #PostgreSQL #SQLTuning #DatabaseOptimization #StatsViaExplainAnalyze #DeepakMahto You can find the full article here: https://postgr.es/p/6Bc

9

2 Comments

SQL Question 43: Top Trending Hashtags Full Video: https://lnkd.in/dTCFmSbR Video 239: This is the 43rd video of the SQL Interview Question series. We are given tweets table which has the twitter or x users and their tweets along with the tweet id and tweet name. tweet_id is the unique identifier for this table We are asked to write a solution to find the top 3 trending hashtags in May 2024. Every tweet may contain several hashtags. Return the result table ordered by count of hashtag, hashtag in descending order. In this video, we approach the solution in five stages and the details are below Stages using Recursive CTE for Extracting Hashtags   1. This approach utilizes a recursive common table expression (CTE) to extract hashtags from tweets.   2. The recursive CTE, named "tags," starts by selecting user IDs, tweet IDs, tweet dates, and the first hashtag found using regular expressions.   3. The "tags" CTE is then recursively applied to extract subsequent hashtags from the modified tweet text until no more hashtags are found.   4. After all hashtags are extracted, another CTE named "all_tags" counts the occurrences of each hashtag and ranks them by count in descending order.   5. Finally, the main query selects the top 3 trending hashtags based on their count and displays them in descending order. For a comprehensive understanding of these SQL methodologies and their application, please refer to this explanatory video. code: https://lnkd.in/d2wYP5md Follow me on, Website : https://growwithdata.co/ YouTube : https://lnkd.in/gSrqE3Nj TikTok : https://lnkd.in/gqFiMex2 LinkedIn : https://lnkd.in/gdfNNX9a Facebook : https://lnkd.in/gU6JWpFH FB Group : https://lnkd.in/gG4ENcrP twitter : https://lnkd.in/geZ3dqi8 Instagram : https://lnkd.in/gn263_PZ WhatsApp : https://lnkd.in/gBa5eC7y TheWide : https://lnkd.in/gt7htz_8 #sql #dataengineers #tablejoins #ceil #floor #bucket #meta #google #facebook #apple #paypal #netflix #amazon #deinterview #sqlinterview #interviewquestions #leetcode #faang #maanga #mysql #oracle #dbms #query #sqlserver #mysql #coderpad #aggregates #aggregation #nonaggregation #database #placementpreparation #lead #lag #windowsfunction #nullcheck #coalesce #sqlperformance #ifnull #case #lead #lag #windowsfunction #tamil #tamilpython #tamilinterview #tamilinterviewlatest #tamilinterviewquestions #sqlintamil

1

Custom date hierarchies are one of the most underutilized features in Tableau. They allow you to define specific drill-downs not automatically available. For example, you may want to allow your users to only drill-down from Year to Month.

167

4 Comments

Don't overwhelm to learn SQL,🙄 SQL is only this much👇😄 1.Intro to SQL • Definition • Purpose • Relational DBs • DBMS 2.Basic SQL Syntax • SELECT • FROM • WHERE • ORDER BY • GROUP BY 3. Data Types • Integer • Floating-Point • Character • Date • VARCHAR • TEXT • BLOB • BOOLEAN 4.Sub languages • DML • DDL • DQL • DCL • TCL 5. Data Manipulation • INSERT • UPDATE • DELETE 6. Data Definition • CREATE • ALTER • DROP • Indexes 7.Query Filtering and Sorting • WHERE • AND • OR Conditions • Ascending • Descending 8. Data Aggregation • SUM • AVG • COUNT • MIN • MAX 9.Joins and Relationships • INNER JOIN • LEFT JOIN • RIGHT JOIN • Self-Joins • Cross Joins • FULL OUTER JOIN 10.Subqueries • Subqueries used in • Filtering data • Aggregating data • Joining tables • Correlated Subqueries 11.Views • Creating • Modifying • Dropping Views 12.Transactions • ACID Properties • COMMIT • ROLLBACK • SAVEPOINT • ROLLBACK TO SAVEPOINT 13.Stored Procedures • CREATE PROCEDURE • ALTER PROCEDURE • DROP PROCEDURE • EXECUTE PROCEDURE • User-Defined Functions (UDFs) 14.Triggers • Trigger Events • Trigger Execution and Syntax 15. Security and Permissions • CREATE USER • GRANT • REVOKE • ALTER USER • DROP USER 16.Optimizations • Indexing Strategies • Query Optimization 17.Normalization • 1NF(Normal Form) • 2NF • 3NF • BCNF 18.Backup and Recovery • Database Backups • Point-in-Time Recovery 19.NoSQL Databases • MongoDB • Cassandra etc... • Key differences 20. Data Integrity • Primary Key • Foreign Key 21.Advanced SQL Queries • Window Functions • Common Table Expressions (CTEs) 22.Full-Text Search • Full-Text Indexes • Search Optimization 23. Data Import and Export • Importing and Exporting Data (CSV, JSON) • Using SQL Dump Files 24.Database Design • Entity-Relationship Diagrams (ERDs) • Normalization Techniques 25.Advanced Indexing • Composite Indexes • Covering Indexes 26.Database Transactions • Savepoints and Nested Transactions • Two-Phase Commit Protocol 27.Performance Tuning • Query Profiling and Analysis • Query Cache Optimization Join this community for Quick instant Preparing for Interview & Placement Drive Updates : https://lnkd.in/d3ahG4-t

3

🚀 𝐀𝐜𝐞 𝐘𝐨𝐮𝐫 𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐰𝐢𝐭𝐡 𝐂𝐨𝐧𝐟𝐢𝐝𝐞𝐧𝐜𝐞! 🚀 Are you prepping for a SQL interview? We've got you covered! 💡 Check out our latest blog on 𝐓𝐨𝐩 50 𝐌𝐨𝐬𝐭 𝐅𝐫𝐞𝐪𝐮𝐞𝐧𝐭𝐥𝐲 𝐀𝐬𝐤𝐞𝐝 𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬 – a must-read for anyone looking to sharpen their SQL skills and land that dream job! 💼✨ Whether you're a beginner or an experienced pro, this guide will help you tackle the most common questions with ease. Learn key concepts, optimize your queries, and boost your confidence before walking into that interview room. 📝 𝐑𝐞𝐚𝐝 𝐧𝐨𝐰: https://lnkd.in/gPbR7eYD Check our website https://lnkd.in/g2UrPpgn for more updates 💬 Connect with Us: WhatsApp Channel - https://bit.ly/3UoSst2 Follow KSR Datavizon for more updates. KONDA REDDY Mahesh Desireddy Santosh J Ajay Babu Inturi Magudeswaran M Satya Sekhar Data Analyst Sekhar Reddy Gopi E Upputuri Gopi Krishna #SQL #SQLInterview #InterviewPrep #KSRDatavizon #TechCareers #DataScience #DataEngineering #SQLTips

5

2 Comments

📊 Mastering SQL Aggregation Functions! 🚀 Today, I’m diving into SQL Aggregation Functions – essential tools for summarizing and analyzing data! 😎✨ 🔍 Why Aggregation? Aggregation functions help us compute useful summaries like total sales, average values, or even count records quickly. 💡 Key Aggregation Functions I'm Exploring: SUM() ➡️ Calculates the total AVG() ➡️ Finds the average MAX() & MIN() ➡️ Identifies the highest and lowest values COUNT() ➡️ Counts the number of records Using these in my projects has made data analysis faster and insights more powerful! 💪📈 👉 Have a favorite SQL function? Let me know in the comments! 👇 #SQL #DataScience #AggregationFunctions #LearningJourney #DataAnalysis #TechTips #DataDriven

1

🚀 Mastering Spark Concepts – From Zero to Big Data Hero! 💡 What is Spark Directed Acyclic Graph (DAG)? Apache Spark’s DAG is the foundation of its execution engine, making data processing optimized, fault-tolerant, and efficient. Let's break it down into simple terms: ✅ What is a DAG? A Directed Acyclic Graph (DAG) is like a roadmap of operations in Spark: ▪️ Nodes: Represent transformations like map or filter. ▪️ Edges: Indicate the flow of data between these operations. ✅ How Does a DAG Work? Here’s how Spark uses DAGs to execute your job: 1️⃣ Job Submission: Spark generates a DAG based on the transformations in your code. 2️⃣ Stages and Transformations: Narrow Transformations (e.g., map, filter): No data shuffling, grouped within the same stage. Wide Transformations (e.g., reduceByKey, join): Involve data shuffling and form boundaries between stages. 3️⃣ Task Execution: Each stage is divided into smaller tasks, distributed across executors for parallel processing. 4️⃣ Failure Handling: If a task fails, Spark recomputes only the affected tasks using DAG lineage, ensuring fault tolerance. ✅ Why is DAG Important? 💥 Efficiency: Minimizes data shuffling, optimizing execution. 🔄 Fault Tolerance: Automatically recalculates failed tasks. ⚡ Speed: Enables parallel execution, resulting in faster processing. 🧠 The DAG is what makes Apache Spark so powerful, ensuring your big data jobs are fast, reliable, and scalable!

26

1 Comment

🚀 Histogram of Tweets [Twitter SQL Interview Question] 🚀 Assume you're given a table Twitter tweet data, write a query to obtain a histogram of tweets posted per user in 2022. Output the tweet count per user as the bucket and the number of Twitter users who fall into that bucket. In other words, group the users by the number of tweets they posted in 2022 and count the number of users in each group. Query:- ===== WITH temp    AS (SELECT t.user_id,         Count(*) AS tweet_cnt      FROM  tweets t      WHERE To_char(t.tweet_date, 'yyyy') = '2022'      GROUP BY t.user_id) SELECT p2.tweet_cnt AS tweet_bucket,     Count(CASE         WHEN p2.tweet_cnt = 1 THEN 1         WHEN p2.tweet_cnt = 2 THEN 1        END)  AS users_num FROM  temp p2 GROUP BY p2.tweet_cnt ; #SQLPracticeSessions #SQLGrowthDaily DataLemur 🐵 (Ace the SQL & Data Interview) For more content, follow Anuj Shrivastav Special thanks to Nick Singh 📕🐒 for creating platforms like DataLemur that sharpen our SQL skills and prepare us for real-world challenges like this one!

6

1 Comment