Voice of the DBA

Tag: data analysis

  • Who’s the Winningest Coach (with AI Help)

    I was listening to the radio the other day and the hosts were discussing the NFL playoffs in 2026. Someone mentioned the winningest coach was Nick Sirianni of the Philadelphia Eagles and no one wanted to face the Eagles. I was wondering if the first part of that was true. I used Claude and SQL Prompt AI to help.

    Note, I enjoyed watching the Eagles lose in the first round to the 48ers. As a lifelong Cowboys fan, that was great. However, I am impressed with Jalen Hurts and was glad to see him win last year.

    This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag. This is part of a series of experiments with AI systems.

    Getting the Data

    First I had to find data. I did find this page of coaching history at Pro Football Reference, which lists coaches, however, I wanted to compare the first few years of their careers, not their totals.

    I decided to see if Claude could help get some data. I started with a query: This pages has a list of nfl coaches: https://www.pro-football-reference.com/coaches/

    I want to loop through each coach and get the details of their career to find the team they coached and the year, returning this in a CSV that has team, year, and coach name. Can you write a python script to do this?

    This kind of worked. I got a script, and it ran, but there were some errors. PFR doesn’t like scrapers and they want to protect their data. I get it.

    2026-01_0117

    I told the AI there was an error and it helped me get the Chrome driver and Selenium module to drive a real browser from automation. I commented out the “headless” part so that I could see it working (a bit).

    2026-01_0118

    This kind of worked, but not really. I got the coaching list and I could see the browser going through each coaches page, as well as the CLI output, but lots of errors. PFR does a good job of blocking this.

    2026-01_0119

    What was amazing is that the script in my repo is something that would have been hours of me messing with different modules and trying to debug the issues. This was literally about 30 minutes of multiple tries before I gave up for the night.

    The next day I decided to give in and just grab data from some coaches that I know have won Super Bowls and had success early in their careers (sorry, Andy Reid). I went to each page and clicked the “CSV export” item and then copy/pasted the data into a file. I then asked the Copilot AI for help. Each set of data was nice, and the file was named for the coach, but the coach’s name wasn’t in there. So I let Copilot edit it.

    2026-01_0196

    Admittedly this wasn’t automated, but by the time I’d created a new file for a new coach, CP had cleaned up my old one. With this in mind, I had 6 coaches of data stored in CSV.

    Back to Claude. No reason not to use Copilot, but I like Claude. I asked it to give me an import script.

    2026-01_0196

    It did, and I saved it (coachimport.py). I ran this, but it errored out. Since this was a file in my repo, I moved back to Copilot, which has access to the files. I asked it to fix the errors. This was the prompt (everything after the colon was pasted in) : I am getting this error in the script: Error importing rawdata\NickSirianni.csv: (‘42000’, ‘[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 24 (“”): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. (8023) (SQLExecDirectW)’)

    It started to examine the files.

    2026-01_0200

    Then it found a problem and started fixing things. Since this is an import and I’ll stage the data, I can let it run and check the results.

    2026-01_0201

    While this was running, I also used SSMS and the Flat File Import to pull in one file from the subfolder to see how well it worked. I basically, next, next, next on this, which was good enough.

    In the time it took me to import 1 file, Copilot had finished and imported all the data. I could query the staging data and see all my rows. I quick check of the different files showed me all rows imported.

    2026-01_0203

    This was with the free Claude web tier and paid corporate Copilot account.

    Now, what do we learn?

    The Analysis

    Since I had the data in a table, I could use SQL Prompt AI to help analyze the data. I started with a simple query:

    SELECT coach, SUM(w) OVER (PARTITION BY coach)
FROM dbo.Coach_Staging

    I could do more, but I’m playing with AI tech, so I then added a prompt: help me analyze this data by comparing each coaches first 5 years with wins, losses and ties and win percentage.

    I got this code:

    SELECT coach,
SUM(W) AS TotalWins,
SUM(L) AS TotalLosses,
SUM(T) AS TotalTies,
CAST(SUM(W) AS FLOAT) / NULLIF(SUM(W) + SUM(L) + SUM(T), 0) AS WinPercentage,
AVG(CAST(Rank AS FLOAT)) AS AverageRank
FROM
(
SELECT coach,
Year,
           W,
           L,
           T,
Rank,
ROW_NUMBER() OVER (PARTITION BY coach ORDER BY Year) AS YearNum
FROM dbo.Coach_Staging
) AS CoachYears
WHERE YearNum <= 5
GROUP BY coach

    That’s about what I would have written. I was about to start adding the row_number when I thought Prompt could help me. I additionally asked for an order by and an average rank, and ended up with a query that made sense.

    The takeaway for me was that I could have written the code, but in a few seconds, SQL Prompt gave me a query I could use. This was way faster than I could have written the query, even with SQL Prompt Intellisense.

    The Results

    Well, is Sirianni the winningest coach in his five years. Here’s what I see.

    2026-01_0210

    By win percentage, Don Shula is better, but there were less games, so Nick Sirianni has more wins and losses, and a slightly lower percentage, but a higher rank. I think it’s fair to say he tops the list, though it depends whether you look at wins or percentage.

    It was also surprising to see Mike Tomlin, who recently left the Pittsburgh Steelers, coming in third.

    An interesting analysis that went way quicker than a few I’ve done in the past. AI is incredibly helpful here, and as I think about all the similar types of queries people have asked for help with over the years, I can see how AI will be very helpful over time.

    Of course, with larger data sets, you’ll want to verify the queries are working with other checks of the data, and you’ll likely want some automated tests on small sets to verify any changes to algorithms still return the correct results.

    If you haven’t tried SQL Prompt, download the eval and give it a try. I think you’ll find this is one of the best tools to increase your productivity writing SQL.

  • How Important Are Real Time Decisions?

    Imagine a perfect world? I have an AI agent that knows my business well. It’s getting real time input from sales, from customers, it makes amazing decisions. We get a large order? We need to ramp up production of our widgets. We have an order pipeline of xx widgets and we know over time that yy% will close. Let’s place a larger order with a supplier overseas.

    The next day, we have an election and tariffs are announced on imported parts. We react immediately, cancel the order, start the process to expand a local factory. We place ads to hire workers and order equipment. Things are looking good for our business and our factory will be up and running in a few months.

    The next week we find out the tariffs weren’t really being enforced, so they’re paused. Our AI agent re-places our large order for imported parts and tried to cancel the factory expansion. Of course, it calculates the costs of both sides before deciding, and perhaps consults with me on other uses of our local factory.

    How many times can we do this? Or rather, how many times would we let an AI agent keep adjusting our business?

    To be fair, humans might do the same thing and over-react, but mostly we become hesitant with unexpected news. That slowness can be an asset. We often need time to think and come to a decision. Lots of our decisions aren’t always based on hard facts, and a lot of business isn’t necessarily fact driven either. We often put our thumb on the scales when making decisions because there isn’t a clear path based on just data.

    Things can get worse when we collaborate. I used to run real-time reports for an importing company, and we found that executives would print a report, get busy, and after minutes (or hours), discuss the report with someone in a department. However, their numbers rarely matched because the reports were printed at different times. At first they lost trust in the system because the same report on the same day had different numbers. Even when we added a “print” or an “as of” time, the reports were too annoying to users to be helpful because the numbers didn’t match.

    Real time isn’t what most of us want. Except in the Olympics. There we want the photo finish right away.

    But not in all sports. A review is good. In the NFL, I’ve come to like instant reply. It’s gotten better/faster and often gives us the right answer. Not always, but often. It’s better, arguably, then just real-time humans.

    Real-time decisions and reactions can be good in some cases. Adjusting machinery, vehicles, electricity, etc. where we need too-quick-for-humans decisions based on data is a good place for real time data. Lots of business decisions we make aren’t the places where we really need real-time insights. Our human brains just don’t work that fast.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Data Analysis Techniques

    This is part of a series on my preparation for the DP-900 exam. This is the Microsoft Azure Data Fundamentals, part of a number of certification paths. You can read various posts I’ve created as part of this learning experience.

    I didn’t think much of this bullet on the DP-900 skills document: describe analytics techniques.

    Learn these concepts. They are definitely part of the knowledge needed. This is a part of some MS Learn courses, like this one.

    Descriptive Analytics

    Descriptive analytics have to do with describing what the data shows. In a report or visualization, we are reporting data to the user. An example of this is showing the top 10 products according to sales.

    These are explanatory analytics and think about that in the test. Are we explaining something.

    Diagnostic Analytics

    A diagnosis is an explanation of why something happened. Diagnostic analytics help explain the reason that something occurred. We might look at the top 10 sales against advertising for products and determine that more advertising shows higher sales.

    In the link above from MS Learn, what we typically do here is identify some anomaly or thing we want to explain, get data related to this, and then use some statistics to find a relationship.

    Predictive Analytics

    As you might guess, this type of analytics relate to the future. We are looking to predict what will happen. An example might be forecasting future demand for services.

    When you see a request to predict, forecast or extrapolate, think predictive analytics.

    Prescriptive Analytics

    This type of analysis was a little harder for me to understand or grasp. I get the general idea, but in prep I missed this one a few times. Prescriptive analytics are developing a prescription for how to change something.

    An example might be if I want to find where to cut costs to impact profit, I am looking for a set of things to change. I need a prescription on what will help me achieve my goal.

    In relation to the exam, this has to do with aiming for a goal (10% more or 12 less) and how to reach that goal. If you see specific numbers, usually that relates to a goal and prescriptive analytics.

    Cognitive Analytics

    This type of analysis has to do with thinking. In data analysis and computing, usually I think ML, AI, or some other type of data mining. This can be deriving conclusions or inferences from data, but the blend between this and the descriptive/diagnostic stuff becomes blurry.

    If you need to do speech to text or video transcription or image recognition, think cognitive.

    Summary

    This is how I viewed this techniques on the exam when answering questions:

    • Descriptive
      – What

    • Diagnostic
      – Why

    • Predictive
      – What will happen

    • Prescriptive
      – What do I do

    • Cognitive – Learning about data in new ways

  • Challenging Trends

    I was studying for the DP-900 exam recently and in one of the Microsoft resources, I ran across this quote: “Historical data is equally important, to give a business a more stabilized view of trends in performance.” This was in context of looking at database performance, but it would apply to any part of your business.

    The world has changed dramatically in the last two years. In early 2020, at this point, I was finalizing work and getting ready for a sabbatical from work. I managed to get the last sabbatical before the world shut down and most people didn’t see the value of one during a pandemic. I returned from sabbatical, with a trip planned to the UK in early March that I canceled. Since that time, I can’t imagine the quote above applies to the airlines’ business. I’ve flown a bit across the last year, but rarely for work. I suspect most of their models and ideas about how to manage their business had to dramatically change.

    Is that quote still applicable to your business? I wonder how many businesses have started to see new trends and needed to abandon some amount of historical data from their charts and graphs. If that’s the case, then is it worth archiving some of that data away, rather than needing to keep it in an online database? Do reports need beginning default dates that start sometime after the pandemic affected the world? Interesting questions.

    For most of us, we don’t necessarily worry about a lot of business impacts. We’ll adjust to what the business analysts ask us to do. However, I wonder if the way you approach your job has changed? For DBAs, have you reset your baseline for how the performance of your systems ought to look during normal times?

    For software developers, many of you are working remotely, so how do you approach work and coordination with others, has that changed? Do you need more or less lead time to work with others and get code tested and deployed? I know many developers have found work during the pandemic, especially remote, to be more enjoyable.

    Lots of things in the world changed, but much of our work continues on the same path. Approaching things with the idea of adapting to change, but using the past as a guide is what has worked well for me. I have strong opinions on how I work and live, but I adapt when there’s a need or evidence to support changes.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.