Using AI to Build SQL Queries | Original
Database Schema (PostgreSQL):
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(100),
last_name VARCHAR(100),
date_of_birth DATE,
gender VARCHAR(10),
phone_number VARCHAR(20),
address TEXT,
city VARCHAR(100),
state VARCHAR(100),
country VARCHAR(100),
zip_code VARCHAR(20),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE,
last_login TIMESTAMP WITH TIME ZONE,
is_active BOOLEAN DEFAULT TRUE,
role VARCHAR(50),
profile_picture_url VARCHAR(255),
bio TEXT,
failed_login_attempts INTEGER DEFAULT 0,
account_balance NUMERIC(10, 2) DEFAULT 0.00
);
above is postgre database, below is table definition. here is natural language query instructions.
I want to get some users who’s firstname is andy, created_ at around the day last month, and who are 20 years old as of 2025, and whose last_login like recent week, give me the sql to query such users
Generated SQL Query:
SELECT *
FROM users
WHERE first_name ILIKE 'andy'
AND CAST(created_at AS DATE) BETWEEN
(CURRENT_DATE - INTERVAL '1 MONTH' - INTERVAL '1 DAY')
AND
(CURRENT_DATE - INTERVAL '1 MONTH' + INTERVAL '1 DAY')
AND EXTRACT(YEAR FROM AGE(date_of_birth)) = 20
AND last_login >= CURRENT_TIMESTAMP - INTERVAL '7 DAYS';