tgindex

Data Analytics

описание

Perfect channel to learn Data Analytics Learn SQL, Python, Alteryx, Tableau, Power BI and many more For Promotions: @coderfun @love_data

← к каналу@sqlspecialist
110 735
подписчиков
Охват к подписчикам
3,0%
ERR
Реакции к просмотрам
0,26%
271 на 33 постов
Пересылки к просмотрам
0,45%
477
Постов в день
2,4
всего 34

Где отзываются чаще

доля реакций к просмотрам
  • 21:30📁 STEP 16 — Build a Portfolio Project 1 — Sales Analytics: Excel + SQL + Power BI → Revenue, Profit, Products, Regions, Customers, Trends Project 2 — Customer Churn: SQL + Python + Power BI → Churn rate, Segments, Retention, Revenue at risk Project 3 — Financial Analysis: Excel + Power BI → P&L, Budget vs Actual, Variance, Trends Project 4 — E-commerce Analytics: SQL + Python + Power BI → Orders, Conversion, AOV, CLV Project 5 — HR Analytics: Excel + SQL + Power BI → Headcount, Attrition, Salary, Tenure 🧠 STEP 17 — Explain Your Projects Business Problem → Data → Cleaning → Transformation → Analysis → Visualization → Insights → Recommendations → Impact 💼 STEP 18 — Build Your Resume 🔎 STEP 19 — LinkedIn & GitHub LinkedIn: Headline, About, Skills, Projects, Certifications, Posts on SQL, Power BI, Excel, Projects, Insights GitHub: SQL projects, Python notebooks, Docs, Screenshots, Data dictionaries, README 🎤 STEP 20 — Interview Preparation Excel: XLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, PivotTables, Power Query SQL: Joins, Aggregations, CTEs, Subqueries, Window functions, Ranking, Running totals Power BI: DAX, CALCULATE, Data modeling, Relationships, Time intelligence Python: Pandas, GroupBy, Merge, EDA Business Cases: Sales drop, Churn increase, Revenue up but profit down, KPI anomaly 🗓️ Double Tap ❤️ For Detailed Explanation1,32%
  • 15 авг.𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to solve this Excel problem. You have the following data: Employee Sales John 12,000 Sarah 18,000 Mike 15,000 David 20,000 Alice 10,000 Find the running total of sales for each employee. 𝗠𝗲: Challenge accepted! 💪 =SUM(B2:B2) Copy the formula down. 💡 Explanation: The formula calculates a cumulative total as you move down the rows. B2 keeps the starting cell fixed. B2 changes as the formula is copied down. Each row adds the current employee's sales to all previous sales. 🎯 Expected Output Example Employee Sales Running Total John 12,000 12,000 Sarah 18,000 30,000 Mike 15,000 45,000 David 20,000 65,000 Alice 10,000 75,000 🚀 Bonus — Using Excel Table References If your data is formatted as an Excel Table named SalesData: =SUM(INDEX(SalesData[Sales],1):[@Sales]) This approach automatically expands as new rows are added to the table. 🚀 Tip for Excel Job Seekers: Running-total questions are common in Excel interviews because they test whether you understand cell references and cumulative calculations. Also practice: • Running totals • Running averages • Monthly cumulative sales • YTD calculations • Cumulative percentages These are frequently used in real-world reporting and dashboards. ❤️ React with ❤️ for more Excel interview challenges!0,90%
  • 15 авг.𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to solve this Excel problem. You have the following data: Employee Department Salary John IT 75,000 Sarah HR 60,000 Mike IT 82,000 David Finance 90,000 Alice HR 65,000 Find the employees whose salary is above the average salary of their department. 𝗠𝗲: Challenge accepted! 💪 =C2>AVERAGEIF(B2:B6,B2,C2:C6) 💡 Explanation: The formula compares each employee's salary with the average salary of their own department. • AVERAGEIF() calculates the average salary for the employee's department. • B2 identifies the current employee's department. • C2 is the employee's salary. The formula returns TRUE when the employee earns more than their department average. 🎯 Expected Output Example Employee Department Salary Above Dept. Average? John IT 75,000 FALSE Sarah HR 60,000 FALSE Mike IT 82,000 TRUE David Finance 90,000 FALSE Alice HR 65,000 TRUE 🚀 Bonus — Return the Employee Name Only In Excel 365: =FILTER( A2:A6, C2:C6>AVERAGEIF(B2:B6,B2:B6,C2:C6) ) This returns the employees whose salaries are above their respective department averages. ❤️ React with ❤️ for more Excel interview challenges!0,78%
  • 21:30🚀 Data Analyst Roadmap 2026 🎯 STEP 1 — Understand the Data Analyst Role What a Data Analyst does: • Data Analytics overview • Data Analyst vs Data Scientist vs Data Engineer • Types of data: Structured vs unstructured • KPIs and metrics • Business questions vs data questions • Descriptive, diagnostic, predictive, prescriptive analytics • Data collection, cleaning, transformation, analysis • Data visualization, reporting, presenting insights • Stakeholder communication 📊 STEP 2 — Master Excel ⏱️ Time: 2–3 weeks Level 1 — Excel Basics Workbook, worksheets, rows, columns, cell references, relative/absolute, formatting, sorting, filtering, freeze panes, find & replace, data validation Level 2 — Essential Formulas SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTBLANK, ROUND, ROUNDUP, ROUNDDOWN Level 3 — Conditional Functions IF, IFS, AND, OR, NOT, IFERROR, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS Level 4 — Lookup Functions XLOOKUP, VLOOKUP, HLOOKUP, INDEX, MATCH, XMATCH Level 5 — Text Functions LEFT, RIGHT, MID, LEN, TRIM, CLEAN, UPPER, LOWER, PROPER, CONCAT, TEXTJOIN, SUBSTITUTE, FIND, SEARCH, TEXT Level 6 — Date Functions TODAY, NOW, DATE, YEAR, MONTH, DAY, DATEDIF, EDATE, EOMONTH, NETWORKDAYS, WORKDAY Level 7 — Advanced Excel PivotTables, PivotCharts, Conditional Formatting, Named ranges, Dynamic arrays, FILTER, SORT, UNIQUE, SEQUENCE, What-if analysis, Goal Seek Level 8 — Power Query Import data, remove duplicates, handle missing values, split columns, merge/append queries, change data types, custom columns, Group By, Basic M 🎯 Excel Project Sales Performance Dashboard: Total Sales, Total Orders, AOV, Sales by Region/Product, Monthly Trend, Top 10 Customers, Sales Growth, Target vs Actual 🗄️ STEP 3 — Master SQL ⏱️ Time: 4–6 weeks Level 1 — SQL Fundamentals SELECT, FROM, WHERE, ORDER BY, DISTINCT, LIMIT, NULL, Aliases, Operators Level 2 — Aggregations COUNT(), SUM(), AVG(), MIN(), MAX(), GROUP BY, HAVING 🔗 STEP 4 — SQL Joins INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN Primary keys, Foreign keys, 1:1, 1:M, M:M relationships 🧠 STEP 5 — Advanced SQL Subqueries, CTEs, Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTILE() CASE, Date functions, String functions, UNION, UNION ALL, INTERSECT, EXCEPT, Recursive CTEs, Conditional aggregation, Running totals, Moving averages, Cohort analysis 🎯 SQL Projects 1. E-commerce Analysis 2. Customer Churn Analysis 3. Financial/Sales Performance Analysis 📈 STEP 6 — Statistics ⏱️ Time: 2–3 weeks Descriptive: Mean, Median, Mode, Range, Variance, Std Dev, Percentiles, Quartiles, IQR Probability: Basics, Conditional probability, Independent events, Bayes' theorem Distributions: Normal, Binomial, Poisson, Skewness Inferential: Population vs Sample, Sampling, Confidence intervals, Hypothesis testing, p-value, Type I/II error, Statistical significance A/B Testing: Control vs Treatment, Null/Alternative hypothesis, Statistical vs Practical significance 📊 STEP 7 — Power BI ⏱️ Time: 4–6 weeks0,64%
  • 6 февр.Keyboard #Shortcut Keys Ctrl+A - Select All Ctrl+B - Bold Ctrl+C - Copy Ctrl+D - Fill Down Ctrl+F - Find Ctrl+G - Goto Ctrl+H - Replace Ctrl+I - Italic Ctrl+K - Insert Hyperlink Ctrl+N - New Workbook Ctrl+O - Open Ctrl+P - Print Ctrl+R - Fill Right Ctrl+S - Save Ctrl+U - Underline Ctrl+V - Paste Ctrl W - Close Ctrl+X - Cut Ctrl+Y - Repeat Ctrl+Z - Undo F1 - Help F2 - Edit F3 - Paste Name F4 - Repeat last action F4 - While typing a formula, switch between absolute/relative refs F5 - Goto F6 - Next Pane F7 - Spell check F8 - Extend mode F9 - Recalculate all workbooks F10 - Activate Menu bar F11 - New Chart F12 - Save As Ctrl+: - Insert Current Time Ctrl+; - Insert Current Date Ctrl+" - Copy Value from Cell Above Ctrl+’ - Copy Formula from Cell Above Shift - Hold down shift for additional functions in Excel’s menu Shift+F1 - What’s This? Shift+F2 - Edit cell comment Shift+F3 - Paste function into formula Shift+F4 - Find Next Shift+F5 - Find Shift+F6 - Previous Pane Shift+F8 - Add to selection Shift+F9 - Calculate active worksheet Shift+F10 - Display shortcut menu Shift+F11 - New worksheet Ctrl+F3 - Define name Ctrl+F4 - Close Ctrl+F5 - XL, Restore window size Ctrl+F6 - Next workbook window Shift+Ctrl+F6 - Previous workbook window Ctrl+F7 - Move window Ctrl+F8 - Resize window Ctrl+F9 - Minimize workbook Ctrl+F10 - Maximize or restore window Ctrl+F11 - Inset 4.0 Macro sheet Ctrl+F1 - File Open Alt+F1 - Insert Chart Alt+F2 - Save As Alt+F4 - Exit Alt+Down arrow - Display AutoComplete list Alt+’ - Format Style dialog box Ctrl+Shift+~ - General format Ctrl+Shift+! - Comma format Ctrl+Shift+@ - Time format Ctrl+Shift+# - Date format Ctrl+Shift+$ - Currency format Ctrl+Shift+% - Percent format Ctrl+Shift+^ - Exponential format Ctrl+Shift+& - Place outline border around selected cells Ctrl+Shift+_ - Remove outline border Ctrl+Shift+* - Select current region Ctrl++ - Insert Ctrl+- - Delete Ctrl+1 - Format cells dialog box Ctrl+2 - Bold Ctrl+3 - Italic Ctrl+4 - Underline Ctrl+5 - Strikethrough Ctrl+6 - Show/Hide objects Ctrl+7 - Show/Hide Standard toolbar Ctrl+8 - Toggle Outline symbols Ctrl+9 - Hide rows Ctrl+0 - Hide columns Ctrl+Shift+( - Unhide rows Ctrl+Shift+) - Unhide columns Alt or F10 - Activate the menu Ctrl+Tab - In toolbar: next toolbar Shift+Ctrl+Tab - In toolbar: previous toolbar Ctrl+Tab - In a workbook: activate next workbook Shift+Ctrl+Tab - In a workbook: activate previous workbook Tab - Next tool Shift+Tab - Previous tool Enter - Do the command Shift+Ctrl+F - Font Drop down List Shift+Ctrl+F+F - Font tab of Format Cell Dialog box Shift+Ctrl+P - Point size Drop down List Ctrl + E - Align center Ctrl + J - justify Ctrl + L - align Ctrl + R - align right Alt + Tab - switch applications Windows + P - Project screen Windows + E - open file explorer Windows + D - go to desktop Windows + M - minimize all windows Windows + S - search0,57%
  • 4 авг.𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to solve this Excel problem. You have the following data: Employee: Joining Date John: 15-Jan-2022 Sarah: 20-Mar-2021 Mike: 10-Jul-2023 David: 05-Nov-2020 How would you calculate the number of years each employee has worked in the company? 𝗠𝗲: Challenge accepted! 💪 Formula: =DATEDIF(B2,TODAY(),"Y") 💡 Explanation: The DATEDIF() function calculates the difference between two dates. B2 is the employee's joining date. TODAY() returns the current date. "Y" returns the number of completed years between the two dates. Copy the formula down to calculate the years of service for all employees. This challenge tests your understanding of: ✅ DATEDIF() ✅ TODAY() ✅ Date Functions ✅ Employee Tenure Calculation 🎯 Expected Output Example Employee: Joining Date: Years of Service John: 15-Jan-2022: 4 Sarah: 20-Mar-2021: 5 Mike: 10-Jul-2023: 3 David: 05-Nov-2020: 5 Results will change automatically as time passes because TODAY() is dynamic. 🚀 Bonus: Calculate Complete Years and Months =DATEDIF(B2,TODAY(),"Y")&" Years "&DATEDIF(B2,TODAY(),"YM")&" Months" Example Output: 4 Years 6 Months 2 Years 3 Months 🚀 Tip for Excel Job Seekers: Date functions are commonly asked in Excel interviews. Make sure you're comfortable with: TODAY(), NOW(), DATEDIF(), EDATE(), EOMONTH(), YEAR(), MONTH(), DAY() These functions are widely used in HR, finance, payroll, and reporting. ❤️ React with ❤️ for more Excel interview challenges!0,51%
  • 10 авг.15 Advanced Excel Shortcut Keys • Navigation 1. Move to last used cell → Ctrl + End 2. Move to first cell → Ctrl + Home 3. Select to last used cell → Ctrl + Shift + End 4. Select to first cell → Ctrl + Shift + Home • Rows Columns 5. Insert entire row → Ctrl + Shift + + 6. Delete entire row → Ctrl + - 7. Hide selected rows → Ctrl + 9 8. Unhide rows → Ctrl + Shift + 9 9. Hide selected columns → Ctrl + 0 10. Unhide columns → Ctrl + Shift + 0 • Formatting 11. Open Format Cells → Ctrl + 1 12. Apply General format → Ctrl + Shift + ~ 13. Apply Number format → Ctrl + Shift + ! 14. Apply Percentage format → Ctrl + Shift + % 15. Apply Currency format → Ctrl + Shift + $ Double Tap ♥️ For More0,51%
  • 6 авг.𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to solve this Excel problem. You have the following data: Employee | Salary John | 75,000 Sarah | 60,000 Mike | 82,000 David | 90,000 Alice | 65,000 How would you return the second highest salary? 𝗠𝗲: Challenge accepted! 💪 =LARGE(B2:B6,2) 💡 Explanation: The LARGE() function returns the Nth largest value from a range. B2:B6 is the range containing salary values. 2 tells Excel to return the second largest value. In this example, the result is 82,000. This challenge tests your understanding of: ✅ LARGE() ✅ Ranking Values ✅ Statistical Functions 🎯 Expected Output Example Formula: =LARGE(B2:B6,2) | Result: 82,000 🚀 Bonus: Return the Employee Name with the Second Highest Salary For Microsoft 365 / Excel 2021: =XLOOKUP(LARGE(B2:B6,2),B2:B6,A2:A6) For older versions of Excel: =INDEX(A2:A6,MATCH(LARGE(B2:B6,2),B2:B6,0)) These formulas return Mike, who has the second highest salary. 🚀 Tip for Excel Job Seekers: Interviewers often ask questions involving the Nth highest or Nth lowest value. Make sure you're comfortable with: • LARGE() • SMALL() • RANK() • SORT() • FILTER() These functions are frequently used in dashboards, reports, and data analysis tasks. ❤️ React with ❤️ for more Excel interview challenges!0,40%
  • 6 авг.𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to solve this Excel problem. You have the following data: Employee Department Salary John IT 75,000 Sarah HR 60,000 Mike IT 82,000 David Finance 90,000 Alice HR 65,000 How would you calculate the highest salary in each department? 𝗠𝗲: Challenge accepted! 💪 For Excel 365 / Excel 2021: =MAXIFS(C2:C6,B2:B6,E2) (Assume cell E2 contains the department name, such as IT.) 💡 Explanation: The MAXIFS() function returns the maximum value that meets one or more conditions. C2:C6 is the salary range. B2:B6 is the department range. E2 contains the department to search for. Excel returns the highest salary for the selected department. This challenge tests your understanding of: ✅ MAXIFS() ✅ Conditional Functions ✅ Data Analysis 🎯 Expected Output Example Department Highest Salary IT 82,000 HR 65,000 Finance 90,000 🚀 Bonus (For Older Excel Versions) =MAX(IF(B2:B6=E2,C2:C6)) Note: In older Excel versions, confirm this as an array formula by pressing Ctrl + Shift + Enter instead of just Enter. 🚀 Tip for Excel Job Seekers: The MAXIFS() and MINIFS() functions are frequently used in business reporting. Make sure you also practice: SUMIFS() COUNTIFS() AVERAGEIFS() MAXIFS() MINIFS() These are among the most commonly tested Excel functions in interviews and are essential for real-world reporting and dashboard creation. ❤️ React with ❤️ for more Excel interview challenges!0,38%
  • 10 авг.𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗿: You have 2 minutes to solve this Excel problem. You have the following data: Employee Department Salary John IT 75,000 Sarah HR 60,000 Mike IT 82,000 David Finance 90,000 Alice HR 65,000 How would you find the average salary of employees who earn more than 70,000? 𝗠𝗲: Challenge accepted! 💪 =AVERAGEIF(C2:C6,">70000",C2:C6) 💡 Explanation: AVERAGEIF() calculates the average of values that meet a specific condition. C2:C6 is the salary range. ">70000" filters salaries greater than 70,000. The result is the average of the qualifying salaries. This challenge tests your understanding of: ✅ AVERAGEIF() ✅ Conditional Calculations ✅ Criteria-Based Analysis 🎯 Expected Output Example Employee Salary John 75,000 Mike 82,000 David 90,000 Average salary: 82,333.33 🚀 Bonus (Multiple Conditions) Find the average salary of employees in the IT department who earn more than 70,000: =AVERAGEIFS(C2:C6,B2:B6,"IT",C2:C6,">70000") This combines multiple criteria using AVERAGEIFS(). ❤️ React with ❤️ for more Excel interview challenges!0,31%
  • 21:30Level 1 — Power BI Fundamentals Desktop, Service, Reports, Dashboards, Workspaces, Data sources, Import mode, DirectQuery, Semantic models Level 2 — Power Query Data cleaning, transformations, merge, append, group, pivot/unpivot, conditional/custom columns, data types 🧮 STEP 8 — DAX SUM, COUNT, COUNTROWS, DISTINCTCOUNT, AVERAGE, MIN, MAX CALCULATE, FILTER, ALL, ALLSELECTED, REMOVEFILTERS, VALUES, SELECTEDVALUE SUMX, AVERAGEX, COUNTX, MINX, MAXX Time Intelligence: TOTALYTD, TOTALMTD, TOTALQTD, SAMEPERIODLASTYEAR, DATEADD, DATESYTD, DATESMTD Measures: YTD, MTD, QTD, Previous Year, YoY %, Running Total, Rolling 12M, Market Share, Contribution % 🏗️ STEP 9 — Data Modeling Fact tables, Dimension tables, Star schema, Snowflake schema, Relationships, Cardinality, Cross-filter direction, Active/Inactive relationships, Role-playing dimensions, Date tables 🎨 STEP 10 — Power BI Visualization Cards, Tables, Matrix, Bar, Column, Line, Area, Scatter, Map, Treemap, Waterfall, KPI, Decomposition Tree, Drill-through, Tooltips, Bookmarks, Buttons, Slicers Data storytelling: What happened? Why? Where? Who/What? What next? 🐍 STEP 11 — Python for Data Analysis ⏱️ Time: 3–4 weeks Basics: Variables, Data Types, Lists, Tuples, Sets, Dicts, If/Else, Loops, Functions, Lambda, Exception Handling NumPy: Arrays, Indexing, Vectorization, Math operations Pandas: DataFrame, Series, read_csv(), read_excel(), head(), info(), describe(), loc[], iloc[], groupby(), merge(), concat(), pivot_table(), sort_values(), drop_duplicates(), fillna(), dropna(), apply() Visualization: Matplotlib, Seaborn: Bar, Line, Histogram, Scatter, Box, Heatmap 🎯 Python Project Customer Sales & Churn Analysis: Cleaning, EDA, Segmentation, Revenue analysis, Churn patterns, Visuals, Recommendations 🧹 STEP 12 — Data Cleaning Missing values, duplicates, wrong data types, outliers, inconsistent categories, invalid dates, bad formats, negative values, duplicate transactions, data integrity Practice in: Excel → Power Query → SQL → Python 🏢 STEP 13 — Business & Domain Knowledge Sales: Revenue, AOV, Conversion Rate, Growth, Gross Margin Marketing: CAC, CTR, CPC, ROAS, Retention Product: DAU, MAU, Retention, Churn, Activation, Engagement Finance: Revenue, Profit, EBITDA, Cost, Margin, Budget vs Actual, Forecast Operations: SLA, Productivity, Turnaround Time, Error Rate, Capacity, Utilization 🤖 STEP 14 — AI for Data Analysts in 2026 Use AI for: SQL help, DAX help, Excel formulas, Python debugging, Data cleaning, Documentation, Storytelling, Root-cause analysis, Hypotheses, Analysis plans Limitations: Hallucinations, Incorrect SQL, Wrong assumptions, Data privacy, Poor context Mindset: AI augments analysts, doesn't replace thinking ☁️ STEP 15 — Cloud & Data Platforms Azure, AWS, Google Cloud, Databricks, Snowflake Concepts: Data warehouse, Data lake, Lakehouse, ETL, ELT, Pipelines, Batch processing, APIs0,26%
  • 11 авг.𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄e𝗿: You have 2 minutes to solve this Excel problem. You have the following data: Employee Department Salary John IT 75,000 Sarah HR 60,000 Mike IT 82,000 David Finance 90,000 Alice HR 65,000 How would you find the second highest salary in the IT department? 𝗠𝗲: Challenge accepted! 💪 =LARGE(FILTER(C2:C6,B2:B6="IT"),2) 💡 Explanation: This formula combines FILTER() and LARGE() to find the second highest salary within a specific department. - FILTER(C2:C6,B2:B6="IT") returns only salaries from the IT department. - LARGE(...,2) returns the second largest value from those salaries. The result is 75,000. This challenge tests your understanding of: ✅ FILTER() ✅ LARGE() ✅ Conditional Filtering ✅ Combining Excel Functions 🚀 Bonus (Without FILTER) For older Excel versions, you can use: =AGGREGATE(14,6,C2:C6/(B2:B6="IT"),2) Here: 14 represents LARGE. 6 ignores errors. B2:B6="IT" filters the calculation to the IT department. 2 returns the second largest value. ❤️ React with ❤️ for more Excel interview challenges!0,26%