Saturday, March 23, 2024

How to calculate Running Total and Running Average in SQL (with PARTITION BY Example)

Hello guys, calculating running totals and running averages is a common requirement in data analysis and reporting but I have interviewed many people who have no clue on how to do this. If you have also seen this question on interview and struggle to answer then you have come to the right place. SQL provides powerful features like window functions,which can be used to compute these running aggregates or running totals. In this article, we'll explore the concepts of running total and running average in SQL and see the real world examples and explanations to help you master these calculations but before that let's find out what is running total actually means?

Sunday, March 10, 2024

Does order of columns matter in composite index? Examples

Hello guys, if you have worked in SQL then you may know that indexes are very important and that's why they are also quite popular on SQL interviews. One of such question which I remember asked to me on interview was "does the order of columns matters in case of composite index?" I mean if you have an index which depends upon two columns the does the order mater like A, B or B,A are same or different. It's tricky question and not many people can answer but we will try in this article.  When it comes to composite indexes, the order of columns can significantly impact how efficiently a database executes queries. In this article, we'll explore the implications of column order in composite indexes and provide examples to illustrate its influence on SQL optimization.

Thursday, March 7, 2024

10 Examples of SELECT Queries in SQL

Hello guys, if you are new to SQL or learning SQL to become a programmer or data scientist then the first thing you should start with SELECT query. I know that many people will say that your first need to know how to create a database, a table, and learn about INSERT,  UPDATE, and DELETE but I suggest you to learn SELECT first. Why? because in 90% cases you will not create new tables, or database but you will be using SELECT queries to retrieve data from existing tables, and I can say from my 20 years of experience in programming and Software development. How to learn SELECT query? Well, the best way is to just execute queries and learn then create requirement like how to find employees whose salary greater than or less than certain amount etc. Once you reach that level, you are good to start with advanced SELECT concepts.