{"id":10682,"date":"2025-01-27T16:04:10","date_gmt":"2025-01-27T21:04:10","guid":{"rendered":"https:\/\/schlaff.com\/wp\/?p=10682"},"modified":"2025-01-29T10:40:33","modified_gmt":"2025-01-29T15:40:33","slug":"struggline-with-sql-how-ai-can-give-you-analytics-superpowers","status":"publish","type":"post","link":"https:\/\/schlaff.com\/wp\/struggline-with-sql-how-ai-can-give-you-analytics-superpowers\/","title":{"rendered":"Are You Struggling with SQL? AI Can Give You Analytics Superpowers"},"content":{"rendered":"
\n
\"\"<\/figure>\n<\/div>\n\n\n


He was just an ordinary product manager, struggling with messy data and failed queries. Day after day, he was constantly thwarted by the complexities of SQL. Queries were unsolvable puzzles\u2014joins, aggregations, and null values tripping him up at every turn. Then, one fateful afternoon, everything changed. He discovered a secret weapon against his SQL struggles\u2014LLMs. With AI by his side, he could finally conquer those inscrutable queries and turn ideas into insights with confidence. <\/em><\/p>\n\n\n\n

And he became… Super SQL Man!<\/em> <\/p>\n\n\n\n

This is his story.<\/em><\/p>\n\n\n\n\n\n\n\n

\n\n\n\n

“Have any of you ever written SQL code as a product manager?” asked the leader of the product meet-up. He continued, “It\u2019s on every job description I\u2019ve seen, but I\u2019ve never seen a product manager write SQL from scratch.”<\/p>\n\n\n\n

I laughed, because I\u2019ve been there. SQL seems deceptively simple\u2014just ask the database for what you need, right? But once you dive in, it\u2019s anything but. Unlike procedural programming, SQL requires you to think differently\u2014focusing on what you want rather than how to get it. And the challenges don\u2019t stop there. Joining tables, ensuring data integrity, handling missing values\u2014it\u2019s all too easy to end up with inaccurate results, duplicates, or painfully slow queries.<\/p>\n\n\n\n

The last time I tried writing SQL code was five years ago. I had a clear vision of what I wanted, and I figured, \u201cI used to code; I can do this.\u201d Four hours later, after wrestling with Microsoft Access, I had nothing to show for it. It was like trying to negotiate a business deal in a foreign country with nothing but a phrasebook\u2014sure, I could order a coffee, but closing the deal? Not a chance. In the end, I sketched out my ideal table structure in Excel, handed it to a developer, and watched them produce the query in five minutes.<\/p>\n\n\n\n

I felt like a kid at school, showing up with oversized crayons and a giant pad of paper\u2014excited but incredibly clumsy. I\u2019d scrawl out my ideas in broad, wobbly strokes, proudly declaring, \u201cI want this!\u201d Then someone more competent would take one look, offer a reassuring nod, and effortlessly translate my messy masterpiece into a precise, elegant SQL query. Once the hard work was done, I\u2019d confidently step in to modify the SELECT statements to get the data I needed.<\/p>\n\n\n\n

But now, with LLMs like ChatGPT, everything is different.<\/strong><\/p>\n\n\n\n

It\u2019s as if I\u2019ve been handed a translator\u2014one that not only understands my crayon sketches but turns them into masterpieces, instantly. I can describe what I need in plain language, and instead of second-guessing every join, filter, and aggregate, I get back a fully formed query that actually works. It\u2019s liberating. SQL, once a frustrating roadblock, has become an accessible tool I can finally wield with confidence.<\/p>\n\n\n\n

\n\n\n\n

As an example, say I\u2019m running an e-commerce site that ships products. Each order moves through different statuses\u2014Ordered<\/code>, Shipped<\/code>, and Delivered<\/code>\u2014all tracked in a table that has the columns status_id<\/code>, product_id<\/code>, status<\/code>, and start_time<\/code>.<\/p>\n\n\n\n

Order Status History<\/strong><\/p>\n\n\n\n

Column Name<\/strong><\/td>Description<\/strong><\/td><\/tr>
status_id <\/td>A unique identifier for each status record.<\/td><\/tr>
product_id<\/td>The identifier for the product associated with the status.<\/td><\/tr>
status<\/td>The current status of the product (e.g., \u201cOrdered,\u201d \u201cShipped,\u201d \u201cDelivered\u201d).<\/td><\/tr>
start_time<\/td>The timestamp marking when the product entered the given status. Note there is no finish time.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

At some point, I realize I need to analyze how long products spend in each status. Sounds easy enough, right? Just subtract timestamps. But once I dig in, things get complicated.<\/p>\n\n\n\n

I only have the start date. How do I get the finish date?<\/strong>
What about orders still in progress?<\/strong>
How do I keep calculations accurate across multiple orders?<\/strong><\/p>\n\n\n\n

And there I am again, crayons in hand, sketching out arrows between statuses and convincing myself it\u2019s just simple math.<\/p>\n\n\n\n

But this time, I have a secret weapon. I plug the following into ChatGPT:<\/p>\n\n\n\n

“I have an order tracking table with the columns status_id, product_id, status, and start_time. I want to calculate the number of days between each status transition for each order. Write a SQL query to pull it back.”<\/em><\/p>\n\n\n\n

And in an instant, I get this:<\/p>\n\n\n\n

WITH StatusTransitions AS (\n  SELECT\n    product_id,\n    status,\n    start_time AS status_start,\n    COALESCE(\n      LEAD(start_time) OVER (PARTITION BY product_id ORDER BY start_time),\n      CURRENT_DATE()\n    ) AS status_end\n  FROM order_tracking\n)\nSELECT\n  product_id,\n  status,\n  status_start,\n  status_end,\n  DATEDIFF(status_end, status_start) AS days_in_status\nFROM StatusTransitions\nORDER BY product_id, status_start;<\/code><\/pre>\n\n\n\n
ChatGPT handled all the complexities seamlessly, knowing how to solve all these problems. Here’s how it did it:<\/p>\n\n\n\n
I only have the start date. How do I get the finish date?<\/strong>
It used the LEAD<\/code> window function to retrieve the start_time<\/code> of the next row for the same product_id<\/code>, providing a clean transition to the next status.<\/p>\n\n\n\n
What about orders still in progress?<\/strong>
For orders without a next status, it incorporated the COALESCE<\/code> function to replace NULL<\/code> values with the current date\u2014ensuring no unfinished orders fall through the cracks.<\/strong><\/p>\n\n\n\n
How do I keep calculations accurate across multiple orders?<\/strong><\/strong>
It applied PARTITION BY product_id<\/code> to ensure each order’s statuses were tracked independently, preventing data from overlapping or getting mixed up.<\/p>\n\n\n\n
It can also explain SQL queries. If I ask my trusty sidekick to explain the query above, I get back:<\/p>\n\n\n\n
    \n
  1. High-level overview:<\/strong> The query calculates how many days a product spends in each status by determining the start and end times for each transition, organized chronologically for each product.<\/li>\n\n\n\n
  2. Why it\u2019s helpful:<\/strong> The query ensures accurate tracking of time spent in each status, even for statuses still in progress.<\/li>\n\n\n\n
  3. Query details:<\/strong> The WITH clause creates a temporary table to calculate status_start and status_end, using LEAD to find the next status\u2019s start time and COALESCE to handle missing end times by substituting the current date. The main query calculates the difference in days between status_start and status_end with DATEDIFF, sorting the results by product_id and status_start.<\/li>\n<\/ol>\n\n\n\n
    \n\n\n\n
    And that\u2019s how I became Super SQL Man! Faster than a query optimizer, more powerful than a recursive join, able to SELECT across vast datasets in a single bound! Once, as a mere mortal product manager, I stumbled through SQL, tangled in a web of missing keys and bewildering errors. But with my AI-powered sidekick, I now navigate the data landscape with ease\u2014solving business problems one query at a time. Stay tuned, data citizens, because Super SQL Man always gets the answer!<\/p>\n\n\n\n
    What once felt impossible\u2014writing SQL with confidence\u2014now feels within reach. No more staring at blank query editors or relying on others to make sense of my data. I focus on the questions that matter and let AI handle the technical details. It\u2019s not about mastering every SQL function, but about having the right tools to turn ideas into insights. <\/p>\n\n\n\n
    If a humble product manager with a crayon and a dream can become Super SQL Man, so can you.<\/p>\n","protected":false},"excerpt":{"rendered":"
    He was just an ordinary product manager, struggling with messy data and failed queries. Day after day, he was constantly thwarted by the complexities of SQL. Queries were unsolvable puzzles\u2014joins, aggregations, and null values tripping him up at every turn. Then, one fateful afternoon, everything changed. He discovered a secret weapon against his SQL struggles\u2014LLMs. With […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[107,75],"tags":[],"class_list":["post-10682","post","type-post","status-publish","format-standard","hentry","category-chatgpt","category-technology"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8wCkz-2Mi","jetpack-related-posts":[{"id":2754,"url":"https:\/\/schlaff.com\/wp\/welcome\/","url_meta":{"origin":10682,"position":0},"title":"Welcome to My Site!","author":"Robert Schlaff","date":"July 26, 2020","format":false,"excerpt":"My Book I wrote a book, Peculiar: Lessons from Amazon's Leadership Principles! Most companies want to be like Amazon but can't figure out why their attempts fall short. My book pulls back the curtain on how Amazon actually works \u2014 from the Leadership Principles that drive its culture to the\u2026","rel":"","context":"In "Home Page"","block_context":{"text":"Home Page","link":"https:\/\/schlaff.com\/wp\/category\/home-page\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/schlaff.com\/wp\/wp-content\/uploads\/2020\/07\/1st-Substack-Image-Fountain-Pen.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/schlaff.com\/wp\/wp-content\/uploads\/2020\/07\/1st-Substack-Image-Fountain-Pen.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/schlaff.com\/wp\/wp-content\/uploads\/2020\/07\/1st-Substack-Image-Fountain-Pen.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/schlaff.com\/wp\/wp-content\/uploads\/2020\/07\/1st-Substack-Image-Fountain-Pen.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":8747,"url":"https:\/\/schlaff.com\/wp\/sam-altmans-tweet-and-the-future-of-chat\/","url_meta":{"origin":10682,"position":1},"title":"Sam Altman’s Tweet and the Future of Chat","author":"Robert Schlaff","date":"January 20, 2024","format":false,"excerpt":"something very strange about people writing bullet points, having ChatGPT expand it to a polite email, sending it, and the sender using ChatGPT to condense it into the key bullet points Sam Altman on Twitter, Mar 2, 2023 Reading Sam Altman's, OpenAI's CEO's, tweet got me thinking about the impact\u2026","rel":"","context":"In "ChatGPT"","block_context":{"text":"ChatGPT","link":"https:\/\/schlaff.com\/wp\/category\/chatgpt\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1108,"url":"https:\/\/schlaff.com\/wp\/the-mother-in-laws-guide-to-cloud-computing\/","url_meta":{"origin":10682,"position":2},"title":"The Mother-in-Law’s Guide to Cloud Computing","author":"Robert Schlaff","date":"May 4, 2018","format":false,"excerpt":"This is part of my \"Mother-in-Law's Guide to Technology.\" My Mother-in-Law is a very smart woman even if she isn\u2019t a \"computer person.\u201d The goal of this post is to take a very big and treacherous sounding idea and bring it down to earth. I tried this before in a\u2026","rel":"","context":"In "MIL Guide to Technology"","block_context":{"text":"MIL Guide to Technology","link":"https:\/\/schlaff.com\/wp\/category\/mil-guide-to-technology\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/schlaff.com\/wp\/wp-content\/uploads\/2018\/05\/Cashiers-Daily-v7-1.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/schlaff.com\/wp\/wp-content\/uploads\/2018\/05\/Cashiers-Daily-v7-1.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/schlaff.com\/wp\/wp-content\/uploads\/2018\/05\/Cashiers-Daily-v7-1.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/schlaff.com\/wp\/wp-content\/uploads\/2018\/05\/Cashiers-Daily-v7-1.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":369,"url":"https:\/\/schlaff.com\/wp\/alexa-and-google-in-our-home\/","url_meta":{"origin":10682,"position":3},"title":"Alexa and Google in Our Home","author":"Robert Schlaff","date":"January 17, 2018","format":false,"excerpt":"In our home, we have a rule that there are no phones at the dinner table. We do this so everyone is paying attention to each other \u2014 not their phones. When a person has a phone at the table, it lets them be alone, even if they are sitting\u2026","rel":"","context":"In "Kids"","block_context":{"text":"Kids","link":"https:\/\/schlaff.com\/wp\/category\/kids\/"},"img":{"alt_text":"","src":"https:\/\/englishatwaterloo.files.wordpress.com\/2013\/02\/students-texting.jpg?w=350&h=200&crop=1","width":350,"height":200,"srcset":"https:\/\/englishatwaterloo.files.wordpress.com\/2013\/02\/students-texting.jpg?w=350&h=200&crop=1 1x, https:\/\/englishatwaterloo.files.wordpress.com\/2013\/02\/students-texting.jpg?w=525&h=300&crop=1 1.5x, https:\/\/englishatwaterloo.files.wordpress.com\/2013\/02\/students-texting.jpg?w=700&h=400&crop=1 2x, https:\/\/englishatwaterloo.files.wordpress.com\/2013\/02\/students-texting.jpg?w=1050&h=600&crop=1 3x, https:\/\/englishatwaterloo.files.wordpress.com\/2013\/02\/students-texting.jpg?w=1400&h=800&crop=1 4x"},"classes":[]},{"id":9436,"url":"https:\/\/schlaff.com\/wp\/hacking-ai-hands-on-ai-security-with-gandalf\/","url_meta":{"origin":10682,"position":4},"title":"Hacking AI: Hands-On AI Security with Gandalf","author":"Robert Schlaff","date":"June 21, 2024","format":false,"excerpt":"Gandalf is one of the most interesting demos I\u2019ve seen. Developed by Lakera, a Swiss AI security firm, Gandalf is designed to teach users about vulnerabilities in large language models (LLMs) like ChatGPT. The demo gamifies AI security by challenging users to extract passwords from a virtual wizard named Gandalf.\u2026","rel":"","context":"In "ChatGPT"","block_context":{"text":"ChatGPT","link":"https:\/\/schlaff.com\/wp\/category\/chatgpt\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":11740,"url":"https:\/\/schlaff.com\/wp\/subscribe-to-my-new-project-ai-for-poets\/","url_meta":{"origin":10682,"position":5},"title":"Subscribe to My New Project: AI for Poets","author":"Robert Schlaff","date":"January 11, 2026","format":false,"excerpt":"Hello loyal readers! I'm starting a new project on Substack called AI for Poets. You can subscribe here. This will be my main writing priority for the year, so I'll be posting there weekly while this blog becomes more occasional. It's similar to what I write here, but focused on\u2026","rel":"","context":"In "ChatGPT"","block_context":{"text":"ChatGPT","link":"https:\/\/schlaff.com\/wp\/category\/chatgpt\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/schlaff.com\/wp\/wp-content\/uploads\/2024\/11\/Teeth-Brushing-300.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/schlaff.com\/wp\/wp-json\/wp\/v2\/posts\/10682","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/schlaff.com\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/schlaff.com\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/schlaff.com\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/schlaff.com\/wp\/wp-json\/wp\/v2\/comments?post=10682"}],"version-history":[{"count":11,"href":"https:\/\/schlaff.com\/wp\/wp-json\/wp\/v2\/posts\/10682\/revisions"}],"predecessor-version":[{"id":10703,"href":"https:\/\/schlaff.com\/wp\/wp-json\/wp\/v2\/posts\/10682\/revisions\/10703"}],"wp:attachment":[{"href":"https:\/\/schlaff.com\/wp\/wp-json\/wp\/v2\/media?parent=10682"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/schlaff.com\/wp\/wp-json\/wp\/v2\/categories?post=10682"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/schlaff.com\/wp\/wp-json\/wp\/v2\/tags?post=10682"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}