SQL Series – This series aim to make sure you are able to learn and understand at least 100 of the most asked concepts in SQL interviews.
Today is Day 2 of the SQL Series – JSON, Date and Time SQL Interview Questions
Bookmark this page, and make sure to be consistent in it. Also, get a notebook and make notes of each question/concept put in here
We have created this series to make you practice and learn ~120 Most Asked SQL interview Questions
Day 1 – Basic SQL Questions + Most Asked SQL Interview Questions
Day 2 – JSON, Date and Time SQL Interview Questions
Day 3 – SQL Query Optimization and Data Modeling Interview Questions
Day 4 – Transactions and Concurrency
Day 5 – SQL Hard Interview Questions
Day 6 – Advanced SQL Aggregation + Advanced Joins
Day 7 – Error Handling & Debugging + Stored Procedures & CTE
Day 8 – Complex Data Handling
Day 9 – Data Modeling
Day 10 – SQL Most Asked Query Interview Questions
200+ Advance SQL Interview Questions
If you have an interview in near future, you can consider getting 220+ SQL Interview questions in the same split as above in our Best Seller e-book – Do check this e-book for all sort of SQL Query, DBMS and Data Modeling Interview Questions
Ace ANY SQL Interview – 200+ Advance SQL Interview Questions – Updated 2026
Extract Nested JSON Array with Filtering in SQL
Ques 1. You are working with an orders table where order_data is stored as a JSON string. Each order contains a nested array of items with price details. Write a query to extract the customer name for all orders where at least one item has a price greater than 150. Assume JSON structure contains customer.name and items array.
SELECT
json_extract_scalar(order_data, '$.customer.name') AS customer_name
FROM orders
CROSS JOIN UNNEST(
CAST(json_extract(order_data, '$.items') AS ARRAY(JSON))
) AS t(item)
WHERE CAST(json_extract_scalar(item, '$.price') AS INTEGER) > 150;Explanation:
In Presto, JSON arrays must be explicitly cast to ARRAY(JSON) before using UNNEST. The json_extract function returns JSON, while json_extract_scalar extracts scalar values as strings. Casting is required for numeric comparisons. This pattern is essential when dealing with semi-structured event or transaction data stored in data lakes like S3 queried via Presto/Trino.
Flatten JSON into Structured Columns
Ques 2. A users table stores user profiles in JSON format inside profile_data. Extract name, age, and city into relational columns for reporting purposes.
SELECT
json_extract_scalar(profile_data, '$.name') AS name,
CAST(json_extract_scalar(profile_data, '$.age') AS INTEGER) AS age,
json_extract_scalar(profile_data, '$.city') AS city
FROM users;Explanation:
Presto does not support ->> like PostgreSQL, so JSON extraction uses JSON path expressions. Scalars must be extracted using json_extract_scalar, and casting is necessary for numeric operations. This transformation is common when preparing raw JSON data for BI tools like Tableau or Power BI, where structured schema is required.
Ques 3: The events table stores timestamps inside a JSON field event_data in ISO 8601 format. Write a query to count the number of events per day.
SELECT
date(from_iso8601_timestamp(json_extract_scalar(event_data, '$.timestamp'))) AS event_date,
COUNT(*) AS total_events
FROM events
GROUP BY 1
ORDER BY 1;Ques 4: Some records in transactions may not contain the amount field inside txn_data. Write a query to safely extract the amount and default to 0 when the field is missing
SELECT
COALESCE(
CAST(json_extract_scalar(txn_data, '$.amount') AS INTEGER),0) AS amount
FROM transactions;Explanation:
json_extract_scalar returns NULL if the key does not exist, which allows COALESCE to provide a fallback value. This is critical in real-world pipelines where JSON schemas are not strictly enforced. Without this handling, aggregations or calculations may produce NULL results or fail.
Calculate Time Difference Between Consecutive Events
Ques 5: For a table user_events with event_time, calculate time difference between consecutive events for each user.
SELECT
user_id,
event_time,
date_diff('second',LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time),event_time) AS time_diff_seconds
FROM user_events;Explanation:
Presto uses date_diff for time calculations instead of direct subtraction. The LAG function retrieves the previous event timestamp within the same user partition. This is essential for sessionization, churn detection, and behavioral analytics.
SQL Date and Time Interview Questions
Basics:
Ques 6: Retrieve all records from orders table where order_date falls within the last 7 days.
SELECT *
FROM orders
WHERE order_date >= current_date - INTERVAL '7' DAY;Explanation:
Presto supports interval arithmetic using INTERVAL syntax. This allows dynamic filtering without hardcoding dates. Such queries are heavily used in dashboards, monitoring systems, and alerting pipelines where rolling time windows are required.
Ques 7: Find how many events occur in each hour of the day.
SELECT
hour(event_time) AS hour_of_day,
COUNT(*) AS total_events
FROM events
GROUP BY 1
ORDER BY 1;Explanation:
Presto provides direct functions like hour() to extract components from timestamps. This is useful for analyzing peak usage patterns, server load, or customer activity cycles. It helps businesses optimize operations based on time-based trends.
Ques 8: Convert a UTC timestamp column event_time to IST
SELECT
event_time AT TIME ZONE 'Asia/Kolkata' AS ist_time
FROM events;Explanation:
Presto handles timezone conversion using AT TIME ZONE. It is crucial to ensure timestamps are stored in UTC and converted at query time. Incorrect timezone handling can lead to serious reporting inconsistencies, especially in global systems.
Generate Continuous Date Series in SQL
Ques 9: Generate a continuous series of dates and join with sales to fill missing dates.
WITH dates AS (
SELECT sequence(DATE '2024-01-01',DATE '2024-01-10',INTERVAL '1' DAY
) AS dt_array
)
SELECT
d AS date,
COALESCE(s.amount, 0) AS amount
FROM dates
CROSS JOIN UNNEST(dt_array) AS t(d)
LEFT JOIN sales s ON s.sale_date = d;7 Days Rolling Average Sales in SQL
Ques 10: Compute rolling 7-day average sales.
SELECT
sale_date,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_avg
FROM sales;Explanation:
Window functions in Presto behave similarly to other SQL engines. This query computes a moving average without joins. It is heavily used in trend analysis, anomaly detection, and forecasting.
The Data Monk Best Seller Books and Services
1. Become a Full Stack Analytics Professional in 2026 – 2200 Interview Questions covering 23 topics
2. Book mock interviews/Career Guidance Session on Top Mate to practice
And below is our master class
1 Month Complete 1:1 Mentorship along with 3 e-books, 4 mock interviews, 4 career guidance sessions, and a complete resume review
You can also go through our The Data Monk’s Youtube Channel with 160+ videos across all the top interview topics
