I have a database table with columns accountid, day, balance. It holds the end-of-day…
- User
I have a database table with columns account_id, day, balance. It holds the end-of-day balances per account, so all accounts have 1 record per day, so account_id+day is UK. I'd like to copy this data into another table with columns account_id, balance, valid_from, valid_to, so if the balance is unchanged between say April 1 and April 10, there is a single row instead of 10, as in the original table. Can you write the SQL that transforms the original data into the new table?
Assistant
Sure! Here's an example SQL query that should do what you're asking for:
CREATE TABLE new_table AS SELECT account_id, balance, MIN(day) AS valid_from, MAX(day) AS valid_to FROM old_table GROUP BY account_id, balance HAVING COUNT(DISTINCT day) > 1;This query creates a new table called
new_tablethat has the same columns as the original table, but with the additional columnsvalid_fromandvalid_to.The
SELECTclause selects theaccount_id,balance, and the minimum and maximumdayvalues for each group of rows that have the sameaccount_idandbalance. TheMINandMAXfunctions are used to get the earliest and latestdayvalues for each group, which will be used as thevalid_fromandvalid_todates.The
GROUP BYclause groups the rows byaccount_idandbalance, and theHAVINGclause filters out any groups that have only one row (i.e., where the balance didn't change between any two adjacent days).Finally, the
CREATE TABLEstatement creates a new table callednew_tablethat has the same columns as the original table, but with the additional columnsvalid_fromandvalid_to.Note that this query assumes that the
daycolumn is of a date or timestamp type. If it's not, you may need to convert it to a date type before using it in the query.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.