Here's one of the most useful SQL keywords you've never heard of:


GROUP BY CUBE

Think of GROUP BY CUBE as the ultimate grouping keyword. It essentially contains GROUP BY ROLLUP and GROUP BY GROUPING SETS inside of it.

How does it work?

Take our example dataset: the Citibike trips dataset. This data contains Citibike trips taken by riders in NYC.

Let's say you're grouping by three different columns: year, month, and station name. You're doing a COUNT() of trips taken.

What are all the possible groupings we could come up with?
1. Total trips overall
2. Trips from each station
3. Trips in each month
4. Trips in each year
5. Trips in each month + each station
6. Trips in each year + each station
7. Trips in each year + each month
8. Trips in each year + each month + each station

GROUP BY CUBE will perform all of these grouping combinations for you automatically.

Usage is super simple. All you need to do is type GROUP BY CUBE along with the names of the columns you'd like to group by.

𝐇𝐨𝐰 𝐭𝐨 𝐩𝐫𝐚𝐜𝐭𝐢𝐜𝐞 𝐝𝐚𝐭𝐚 𝐯𝐚𝐥𝐢𝐝𝐚𝐭𝐢𝐨𝐧 𝐚𝐬 𝐚𝐧 𝐚𝐬𝐩𝐢𝐫𝐢𝐧𝐠 𝐝𝐚𝐭𝐚 𝐚𝐧𝐚𝐥𝐲𝐬𝐭?

Here's a step-by-step guide for the same:

Step 1️⃣ - Download a practice dataset. I'd recommend the Codebasics resume project challenge dataset (as it contains multi-table datasets).

Step 2️⃣ - Open your preferred RDBMS tool (SQL server/MySQL). Create a local database to load the dataset.

Step 3️⃣ - Import the practice dataset (.xlsx/.csv) into this database by creating the tables (please google if you need help).

Step 4️⃣ - Now open Power BI desktop and connect to the local database using the appropriate connector.

Step 5️⃣ - Build the dashboard using the questions shared in the resume project challenge.

Step 6️⃣ - Now, you can validate the output of your dashboard by writing SQL queries.

Step 7️⃣ - Try to write an SQL query for a question asked in the challenge. You need to convert a natural language question into an SQL query.

Step 8️⃣ - Compare the query output with the dashboard output and check if the numbers are matching. If they aren't matching, either the query is wrong or the dashboard numbers are wrong. Hence, try to identify the gap.

Step 9️⃣ - Repeat the process for every question asked in the challenge.

Thus, you will learn and practice both SQL and Power BI simultaneously.

𝐖𝐡𝐲 𝐬𝐡𝐨𝐮𝐥𝐝 𝐲𝐨𝐮 𝐭𝐫𝐲 𝐭𝐡𝐢𝐬 𝐦𝐞𝐭𝐡𝐨𝐝?

In real-world scenarios, 𝐝𝐚𝐭𝐚 𝐯𝐚𝐥𝐢𝐝𝐚𝐭𝐢𝐨𝐧 is a very important step in every analytics project. One needs to compare the output of the report/dashboard with the data source and then launch it for usage, to avoid discrepancies.

This will help you weed out any mistakes that you have applied in your report/dashboard logic.

Best Telegram Channel for Data Analysts: https://news.1rj.ru/str/sqlspecialist

Q1: How do you ensure data consistency and integrity in a data warehousing environment?

Ans: I implement data validation checks, use constraints like primary and foreign keys, and ensure that ETL processes have error-handling mechanisms. Regular audits and data reconciliation processes are also set up to ensure data accuracy and consistency.

Q2: Describe a situation where you had to design a star schema for a data warehousing project.

Ans: For a retail sales data warehousing project, I designed a star schema with a central fact table containing sales transactions. Surrounding this were dimension tables like Products, Stores, Time, and Customers. This structure allowed for efficient querying and reporting of sales metrics across various dimensions.

Q3: How would you use data analytics to assess credit risk for loan applicants?

Ans: I'd analyze the applicant's financial history, including credit score, income, employment stability, and existing debts. Using predictive modeling, I'd assess the probability of default based on historical data of similar applicants. This would help in making informed lending decisions.

Q4: Describe a situation where you had to ensure data security for sensitive financial data.

Ans: While working on a project involving customer transaction data, I ensured that all data was encrypted both at rest and in transit. I also implemented role-based access controls, ensuring that only authorized personnel could access specific data sets. Regular audits and penetration tests were conducted to identify and rectify potential vulnerabilities.