Our team runs an AI chatbot service. Every time a user starts a conversation, one row is added to the chats table (who started it, and when). The service is used across many countries, so the timestamps (started_at) are stored in UTC. Write a single SELECT statement that builds the "chats per day" report the ops team looks at every morning. The ops team works in Korea, so the report dates are in Korean time (Asia/Seoul). The result has two columns: - report_date : the date (in Korean time) - chat_count : the number of conversations started on that date Write your query in solution.sql, and pressing 'Run test' runs it against a real database and shows the result table.
This challenge has you write a PostgreSQL SELECT statement for the "chats per day" report of an AI chatbot service. A table of conversation records comes ready with data, and pressing 'Run test' runs your query against a real database and shows the result table. The result has two columns: one for the date, one for the number of conversations started on that date.
This problem is about "the same moment belongs to different dates depending on the basis you use." When a service stores timestamps in a common worldwide basis (UTC) and you aggregate by date, the numbers can look fine on daytime rows yet come out different for rows near midnight. Before trusting such a query, don't just skim a few ordinary daytime rows — run it for real and watch with your own eyes that the data sitting around the day boundary is grouped onto the date you intended.