Smartwyre’s Post

---- 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐭𝐢𝐜𝐬 𝐮𝐬𝐢𝐧𝐠 𝐒𝐐𝐋 --------- ---- 𝐏𝐨𝐬𝐭 #17: ✅ 𝐖𝐢𝐧𝐝𝐨𝐰 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬 𝐢𝐧 𝐒𝐐𝐋 -------- -Window functions applies aggregate, ranking & analytical functions over a particular window(set of rows). -OVER() clause is used with windows function to define that window. -The difference between windows() and aggregate function is that in aggregate function, we geta single ouitput while in windows, maintain their seperate identities. 𝑺𝒚𝒏𝒕𝒂𝒙-   select column_name, func() OVER([<partition clause>] [Order by clause] [<Row or Range clause>]) from table name; 𝑾𝒊𝒏𝒅𝒐𝒘𝒔 𝒇𝒖𝒏𝒄𝒕𝒊𝒐𝒏 𝒕𝒚𝒑𝒆𝒔- (a) Aggregate functions - Sum, Avg, Count, Min, Max (b) Ranking Functions - Row_number, Rank, Dense_Rank, Percent_rank (c) Value/Analytic - Lead, Lag, First_value, Last_value  1. ROW_NUMBER() Assigns a unique integer to each row within a result set.Example: Assign row numbers to employees based on their salaries. Example- SELECT employee_id, first_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM employees; 2. RANK() Assigns a rank to each row within a result set, handling ties with the same rank.Example: Rank employees by their salaries. SELECT employee_id, first_name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; 3. DENSE_RANK() Similar to RANK(), but assigns consecutive ranks without gaps for tied values.Example: Assign dense ranks to employees based on their salaries. SELECT employee_id, first_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank FROM employees; 4. NTILE() Divides rows into specified number of approximately equal-sized buckets.Example: Divide products into quartiles based on their prices. SELECT product_id, product_name, price, NTILE(4) OVER (ORDER BY price) AS quartile FROM products; 5. LEAD() / LAG() Accesses values from subsequent (LEAD) or previous (LAG) rows in a result set.Example: Calculate temperature change between consecutive readings. SELECT reading_time, temperature, LAG(temperature) OVER (ORDER BY reading_time) AS prev_temp, LEAD(temperature) OVER (ORDER BY reading_time) AS next_temp FROM temperature_readings; 6. NTH_VALUE() Retrieves the value of a column from the Nth row within a window.Example: Get the second highest salary in each department. SELECT department_id, NTH_VALUE(salary, 2) WITHIN GROUP (ORDER BY salary DESC) AS second_highest_salary FROM employees GROUP BY department_id; 7. PERCENT_RANK() Calculates the relative rank of a row compared to others within a window.Example: Calculate the percent rank of employees based on their salaries. SELECT employee_id, first_name, salary, PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank FROM employees; #DataAnalyst #SQL #WindowFunctions #SQLDeveloper #DBMS

Thanks Nate Robinson for the shout out. Contractors will never replace full time data employees, but they are tremendously useful in cases where resources and time are tight, and sourcing an FTE is difficult. I’d love to hear other data leaders’ opinions on when to choose contracting as a solution and when to hire and FTE. If you have an opinion, drop a comment below!

**Primary Key vs. Foreign Key** In the world of databases, two critical concepts that ensure the integrity and proper organization of data are Primary Key and Foreign Key. Let's break down their roles and importance. 🔑 Primary Key (PK): The primary key is the backbone of a table's structure. It uniquely identifies each row in a table, ensuring that no two records can be the same. It prevents duplication and helps in retrieving, updating, or deleting records with precision. **Example**: Consider a table of employees: | EmployeeID | Name | Department | |————- ----|---------|------------| | 1 | Alice | HR | | 2 | Bob | IT | | 3 | Alice | IT | Even though two employees have the same name, their *EmployeeID* (the primary key) uniquely distinguishes them. This helps the system accurately manage individual records, such as salary, attendance, or performance. 🔗 Foreign Key (FK): While a primary key keeps a table organized, a foreign key ensures the relationship between two tables remains accurate. It links records in one table to records in another, establishing a meaningful connection. The foreign key also enforces *referential integrity*, meaning it prevents actions that would leave orphaned records. **Example**: Imagine two tables: **Customers** and **Orders**. In the Orders table, the foreign key (CustomerID) connects back to the *CustomerID* in the Customers table. This way, each order is correctly assigned to a specific customer. *Customers Table*: | CustomerID | CustomerName | |------------|--------------| | 101 | Alice | | 102 | Bob | *Orders Table*: | OrderID | OrderDate | CustomerID (FK) | |---------|-----------|-----------------| | 5001 | 2024-09-01| 101 | | 5002 | 2024-09-02| 102 | Here, the **CustomerID** in the Orders table acts as a foreign key. It ensures that every order is associated with a valid customer. If we tried to insert an order with a non-existent CustomerID, the database would reject it, preserving data integrity. 💡 **Why Does This Matter?** - **Primary Key** ensures that data within a table is unique and easily accessible. - **Foreign Key** ensures that data between related tables is consistent and accurate, preventing errors like creating orders for non-existent **Hopefully you like this🙂**

📈 Does your business rely on spreadsheets for rotas? It’s time to rethink your strategy. Discove the risks and why an advanced solution is essential for workforce management. 🔗 Read the full article: https://d36.co/1bRbk #WorkforceManagement #RotaManagement

📊 Spreadsheets might be convenient for rotas, but are they safe? Uncovers the risks and limitations of using spreadsheets for workforce management. 🔗 Explore the insights: https://d36.co/1bR4f #WorkforceManagement #RotaManagement

Spreadsheets may offer a straightforward/cost-effective solution for creating staff rotas, yet their reliability and security in managing workforce tasks are subject to risks and shortcomings. This exploration sheds light on the potential dangers and limitations inherent in the use of spreadsheets for handling staff schedules. #NHS #GP #rotamanagement #digitaltransformation #medicalpractice #efficiency #riskmitigation

Check out the latest blog post on the benefits of contractors/consultants for workforce enhancement by Kevin Koenitzer!

I was in London last month and one topic kept coming up, DATA! On the Wednesday I went to Accountex where the hot topic was AI data analysis and all the software advances in this area. On Wednesday night I caught up with the marketing director at the O2 (more posts on that to follow) who knows how I love business numbers so was talking me through their data, and how their marketing was helping to drive their financial growth. And then on Thursday I met with a client who has ambitions plans for growth but was needing reassurance over cashflow and affordability for hiring decisions. Again, accurate data (pipeline) was the topic of conversation. But in an era when data overload can be as problematic as a lack of data, it’s key to ascertain what’s worth looking at and what’s worth ignoring. To do this you need to consider what decisions you expect to need to make over coming year and what data you need to help speed those decisions up. When considering data, I always think of a quote from Barack Obama, he said: "You don't have to get to 100% certainty on decisions, get to 51%, and when you get there, make the decision and be at peace with the fact that you made the decision based on the information you had" One thing I would add is data driven businesses can get better than 51% sure on decisions, but speed of decision is often more important than increased certainty. Whatever you do, don’t let decision paralysis stop you from taking action. If you need help making accurate and timely decisions, get in touch.

The Numbers Don't Lie: Why Outsourcing Data Analytics Can Be a Smart Business Move In today's data-driven world, unlocking the power of your information is crucial. But building an in-house data analytics team can be expensive and time-consuming. Here's why partnering with an outsourced data analytics team can be a strategic and cost-effective solution! Cost Savings: 🔸 Recruitment & Onboarding: Skip the costs of advertising, interviewing, and training new hires. 🔸 Infrastructure & Tools: Outsource partners already have the necessary software and hardware, saving you significant upfront investment. 🔸 Scalability: Pay for the expertise you need, when you need it. No need to maintain a large, permanent in-house team. Beyond the Bottom Line: - Access to Top Talent: Tap into a global pool of experienced data analysts, often specializing in specific industries or niche areas. - Faster Time to Insights: Get started quickly with a team already up-to-speed and ready to tackle your needs. - Focus on Core Business: Free up your internal resources to focus on your core competencies and strategic initiatives. Outsourcing data analytics isn't just about saving money, it's about gaining a competitive edge through efficient access to expert resources. And this is exactly what we provide in Valiotti Analytics. Contact us or hit me up on my DMs to discuss your project! #DataAnalytics

📊 Spreadsheets might be convenient for rotas, but are they safe? Uncovers the risks and limitations of using spreadsheets for workforce management. 🔗 Explore the insights: https://d36.co/1bRdR #WorkforceManagement #RotaManagement