May 2019 T-SQL Tuesday #114: Puzzles

This month’s T-SQL Tuesday is hosted by Matthew McGiffen. His challenged is to come up with a puzzle.  My entry involves a simple request that is more difficult to solve than it seems on the surface.

TSQL2SDAY-300x300

I was recently asked by one of my siblings during a family party how to find the row with the maximum count from a dataset. Yes, I do have that geeky of a family. He was working with MySQL, but I figured the solution would be similar to T-SQL. Since neither type of database was handy during dinner, I just explained that you have to separate the logic with something like a subquery.

He had his own data, but I’ll explain with a similar scenario in AdventureWorks. The requirement is to find the customer and order count for the customer or customers who have placed the most orders. You could write a query like this which returns the rows first along with all the other rows.

 

SELECT CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY COUNT(*) DESC;

One row is returned for each customer, not exactly what is needed.

puzzle1

You could use TOP to return just one row, but this is still not accurate since two customers have the maximum count, and only one of them will show up in these results.

SELECT TOP(1) CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY COUNT(*) DESC;

puzzle2

If nesting aggregate functions was possible, you could filter in the HAVING clause. This query won’t work!

SELECT TOP(1) CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
HAVING COUNT(*) = MAX(COUNT(*));

puzzle3

What about using a CTE (common table expression) or derived table to find the customers and counts first? You might think a query like this would work:

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID, CountOfOrders, MAX(CountOfOrders) AS MaxCount
FROM Counts
GROUP BY CustomerID, CountOfOrders
HAVING CountOfOrders = MAX(CountOfOrders);

puzzle4

Unfortunately, since it’s grouped by CustomerID and CountOfOrders, the Max(CountOfOrders) is returned for each group. This is not the solution.

What about my favorite type of function, windowing functions? By adding an OVER clause to MAX(CountOfOrders), the overall max count can be returned.

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID, CountOfOrders, MAX(CountOfOrders) OVER() AS MaxCount
FROM Counts
GROUP BY CustomerID, CountOfOrders
HAVING CountOfOrders = MAX(CountOfOrders);

puzzle5

The problem is that you cannot directly filter a windowing function. This returns an error:

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID, CountOfOrders, MAX(CountOfOrders) OVER() AS MaxCount
FROM Counts
GROUP BY CustomerID, CountOfOrders
HAVING CountOfOrders = MAX(CountOfOrders) OVER();

puzzle6

The beauty of window aggregates is that you can have multiple levels of aggregation in the same query. What about moving the MAX(Count) to the CTE? You may think this looks strange since you can’t nest aggregate functions, but in this case, the window function is operating on an aggregate function!

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders,
MAX(COUNT(*)) OVER() AS MaxCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID, CountOfOrders, MaxCount
FROM Counts;

puzzle7

The only thing left to do is to add a filter, and this time it will work since the aggregations are completed in the CTE.

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders,
MAX(COUNT(*)) OVER() AS MaxCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID, CountOfOrders, MaxCount
FROM Counts
WHERE CountOfOrders = MaxCount;

puzzle8

There are many other ways of solving this. You could use a view or a derived table (subquery) for example, but I think this is the best method. Another thing to note is that the solution will require three levels if you don’t use the windowing function. Here is an example:

WITH Counts AS (
SELECT CustomerID, COUNT(*) CountOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
),
MaxCount AS (
SELECT MAX(CountOfOrders) AS MaxCount
FROM Counts
)
SELECT CustomerID, CountOfOrders, MaxCount
FROM Counts
CROSS APPLY MaxCount
WHERE CountOfOrders = MaxCount;

puzzle9

The first level returns the list of customers and counts. The second finds the overall maximum count. The outer query uses CROSS APPLY to join the two CTEs. Now that the aggregation is completed before the outer query, the filter works.

I thought this was a nice puzzle for T-SQL Tuesday because the initial requirements sound so simple. The solution is not difficult but also not intuitive.

 

Posted in Uncategorized | 4 Comments

Celebrating Women in Tech

NOTE: This post was originally published on Simple-Talk.

NASA recently named a facility in honor of Katherine Johnson, the mathematician who performed the calculations for space flights in the 50s and 60s. She was one of the “human computers” whose stories were made famous by the movie Hidden Figures just a few years ago. Until that movie was released, the contributions of these women were not well known. Imagine making those calculations by hand today!

There are many women pioneers in technology whose accomplishments outshine anything most of us could do these days. Ada Lovelace wrote the first computer program for a theoretical machine about 100 years before the first real computer existed. Admiral Grace Hopper developed one of the first compilers. The inventions of Hedy Lamarr during World War II lead to the Wi-Fi technologies we all now use.

At the dawn of the computing age, women coders were the norm. Most of the programmers on the ENIAC system were women, for example. Programming was considered low-prestige, unglamorous, and tedious work most suited for women, but even then, women didn’t get credit for their achievements.

Many things have changed over the decades, including the attitudes towards programming. It’s now a highly prized and well-paying skill. Development teams are more likely to be comprised solely of men. There are many factors that have led to this disparity, but the turning point seems to be the 1980s during the rise of home computers. Since then, the number of women in computer science university programs has decreased with a subsequent drop in women working in technology fields.

Women have always had the brain power to work in technology, but they are often discouraged from pursing tech careers by the very people who should be supporting them. Toxic “brogrammer” cultures or inflexible work environments can also contribute to making the women who work in tech drop out. It’s not easy working on a team where you don’t feel welcome, and fixing these problems is good for everyone, not just women.

When you think about how much easier coding is today with modern languages, you must realize these pioneering women had mad skills. Women have made a big difference in tech throughout history, but their stories are not always told. While the numbers are smaller, women continue to innovate and lead.

Do you enjoy your technology career? Be sure to thank the women tech pioneers whose accomplishments have made your life easier!

Posted in Uncategorized | Leave a comment

Three Books That Have Influenced My Career

(Originally published on Simple Talk at https://www.red-gate.com/simple-talk/blogs/three-books-that-have-influenced-my-career/)

I’m a lifelong learner. One of the ways I love to learn is by reading books. While I was making the transition to software development from my original career, I read the book “Code Complete” by Steve McConnell. This was back in the mid-90s before the Agile Manifesto was written or DevOps was a thing.

Codecomplete

I did learn a ton from that book, however. It taught me the importance of making code readable with the use of naming conventions, formatting, and comments. Software methodology has evolved quite a bit since then, but these concepts are still important over 20 years later.

Once I became a DBA, I read the book “The 7 Habits of Highly Effective People.” This is not necessarily a book about technology, but there are quite a few habits that apply. My favorite lesson was about being proactive. As a DBA, it was important to spend time creating scripts to automate tasks. I had a rule that I didn’t want to do something manually three times, so I would try to find a way to automate it before I was asked the third time.

7habits

Another lesson that I gleaned from that book is that a tool that can help you do your job, maybe a monitoring tool for SQL Server, is going to save time and money in the long run. And when automating a task, a tool that can write a script for you is going to save development time and decrease the chance of errors.

Finally, I read the book “The Phoenix Project” about five years ago. This book is different. Instead of a pure technical or self-help book, it’s a novel. It’s the story of a company that has been working on this important project, called the Phoenix Project, for years. It’s late, over budget, and hasn’t been tested. Unfortunately, the company decides it must roll it on a certain date out to disastrous consequences.

phoenix

This book is the story of how a company embraces DevOps and saves the organization. There is also that one guy, named Brent in this story, who knows more about the infrastructure than anyone else. He is both a valuable asset and one of the reasons that the IT department is in such a mess since everyone depends on him so much. From this book, I learned don’t be a Brent, but also what is required to embrace DevOps, from developing trust between the teams, to automating deployments, to having a good change management system. All three of these books are worth reading if you have the time.

By the way, if you are interested in learning more about DevOps, be sure to sign up for Redgate’s SQL in the City Streamed. This is a free online event, and I’ll be presenting a session along with some fantastic speakers like Kendra Little, Grant Fritchey, Steve Jones, and The Two Chrises. I hope you can join us!

 

Posted in Life in IT | Leave a comment