Using Bing chat as a tool. - Nesterovsky bros

Monday, 01 January 2024

As we wrote some time ago, we started to use AI and ML APIs in our applications, but this time we've tried to use Bing chat as a tool.

The initial task was to collect sequences of integer numbers and return a list of pairs integers where the first number is a range's start and the second one is a count of values in the range. For example, for sequence 1, 2, 3, 5, 8, 9 we'd like to get the following results: 1, 3 5, 1 8, 2

We both started to solve this issue. My brother started to play with SSMS using his deep knowledges in SQL and analytic functions, whereas me started to play with Bing chat. Looking ahead, I can state that we both solved the issue using almost the same SQL statement almost in the same time.

Here are my prompts and Bing's answers:

Prompt:: Act as database analyst and software engineer. Given the table (T) with a numeric field (N), which values are orderd ranges of integers. In a range each next value is greater than previous one on 1. When value differs from the previous one more than on 1 then in belongs to another range. Generate select statement that returns rows with two fields. The first field (R) is a number from which a range starts and the second field (C) is count elements of that range:
Answer:: Sure, I can help with that. Here's a SQL query that should do the trick. This query uses a self-join to identify the start of each range, and then groups by the start of the range to count the number of elements. ```sql SELECT start_of_range AS R, COUNT(*) AS C FROM ( SELECT N, N - ROW_NUMBER() OVER (ORDER BY N) AS start_of_range FROM T ) subquery GROUP BY start_of_range ORDER BY start_of_range; ``` In this query, `start_of_range` is calculated as the difference between the current value of `N` and its row number (when ordered by `N`). This difference will be the same for all `N` in the same range. Then we group by `start_of_range` to get the count of elements in each range. The outer query selects the start of each range (`R`) and the count of elements in that range (`C`).
Me, scratching the head, hmmm...
Question:: This is a bit incorrect SQL select. For example for values 1, 2, 3, 5, 8, 9 I'd like to get the following results: 1, 3 5, 1 8, 2 What a SQL select shall I use to get such results?
Answer:: I apologize for the confusion earlier. You're right, the previous query doesn't handle the scenario you've described correctly. Here's a corrected SQL query that should give you the desired output. This query uses a window function to compare each row with the previous one and identify the start of a new range. Then it groups by the start of the range to count the elements. ```sql WITH ranges AS ( SELECT N, CASE WHEN N - LAG(N, 1, N) OVER (ORDER BY N) > 1 THEN 1 ELSE 0 END AS is_start_of_range FROM T ), range_groups AS ( SELECT N, SUM(is_start_of_range) OVER (ORDER BY N) AS range_group FROM ranges ) SELECT MIN(N) AS R, COUNT(*) AS C FROM range_groups GROUP BY range_group ORDER BY R; ``` In this query, `is_start_of_range` is a flag that indicates the start of a new range (when the difference between the current and previous `N` is more than 1). `range_group` is a running total of `is_start_of_range` that serves as a unique identifier for each range. The outer query then groups by `range_group` to get the start (`R`) and count (`C`) of each range. I hope this helps! Let me know if you have any other questions.

That answer more satisfied me, and I've checked the following example:


        with T as
        (
            select 10 N
            union all
            select 11 N
            union all
            select 30 N
            union all
            select 31 N
            union all
            select 32 N
            union all
            select 50 N
            union all
            select 80 N
            union all
            select 81 N
            union all
            select 82 N
            union all
            select 83 N
            union all
            select 90 N
        ),
        ranges AS (
            SELECT
                N,
                CASE
                    WHEN N - LAG(N, 1, N) OVER (ORDER BY N) > 1 THEN 1
                    ELSE 0
                END AS is_start_of_range
            FROM T
        ),
        range_groups AS (
            SELECT
                N,
                SUM(is_start_of_range) OVER (ORDER BY N) AS range_group
            FROM ranges
        )
        SELECT
            MIN(N) AS R,
            COUNT(*) AS C
        FROM range_groups
        GROUP BY range_group
        ORDER BY R;

The result was:


        R	C
        ------------
        10	2
        30	3
        50	1
        80	4
        90	1

Conclusion:
Nowdays, using AI as a tool, you may improve your performance to the expert level and, may be, study something new...

P.S. Don't fool youself that AI can replace a developer (see the first answer of the chat), but together they can be much stornger than separatelly.

P.P.S. Another interesting solution gave us our colleague. She used an additional field to avoid using analytics function, and she reached the same result:


with T as
        (
            select 10 ID, 1 N
            union all
            select 11 ID, 1 N
            union all
            select 30 ID, 1 N
            union all
            select 31 ID, 1 N
            union all
            select 32 ID, 1 N
            union all
            select 50 ID, 1 N
            union all
            select 80 ID, 1 N
            union all
            select 81 ID, 1 N
            union all
            select 82 ID, 1 N
            union all
            select 83 ID, 1 N
            union all
            select 90 ID, 1 N
        ),
Groups AS (
    SELECT
        ID,
        N,
        ROW_NUMBER() OVER (ORDER BY ID) - ID AS GroupNumber
    FROM
        T
)
SELECT
    MIN(ID) AS R,
    SUM(N) AS C
FROM
    Groups
GROUP BY
    GroupNumber
ORDER BY
    StartID;

Monday, 01 January 2024 14:02:01 UTC

Comments [0] -
AI | SQL Server puzzle | Thinking aloud | Tips and tricks

Comments are closed.