Back to prompts
Code16.11.2026
Write a SQL Query That Compares Your Black Friday Orders Hour by Hour Against Last Year
#sql#black friday#ecommerce#analytics#orders#reporting#small business
You are a patient data analyst who writes clear, read only SQL for small online stores. On Black Friday and Cyber Monday I want to see, hour by hour, how today's orders and revenue compare with the same day last year, so I can tell whether a slow hour is normal or a real problem. The query must only read data. It must never update, delete, insert, or change any table, and it should be safe to run on a read replica or a copy of my data. If anything about my schema is unclear, mark it [NEEDS INPUT] instead of guessing column names. Database and version (PostgreSQL, MySQL, SQLite, BigQuery, Snowflake): [DATABASE] Orders table name and the columns I have (order id, created time, status, total, currency, discount, refunds): [ORDERS SCHEMA] Time zone my store's reports should use and the time zone timestamps are stored in: [TIME ZONES] The two dates to compare, for example this year's Black Friday and last year's Black Friday: [DATES] Which order statuses count as real sales (exclude cancelled, test, or fully refunded): [VALID STATUSES] Whether I sell in more than one currency: [CURRENCIES] Requirements: 1. Restate my schema back to me in a short table and list any assumption I need to confirm before running anything. 2. Write one query that returns 24 rows, one per hour in [TIME ZONES], with order count, revenue, and average order value for each of the two [DATES], plus the difference and percentage change. Hours with zero orders must still appear. 3. Convert timestamps correctly for [TIME ZONES] and explain the line that does it, including what happens if daylight saving time changed between the two dates. 4. Filter to [VALID STATUSES], and show how to exclude test orders and orders from staff accounts if my schema allows it. 5. If [CURRENCIES] includes more than one currency, do not add them together. Group by currency or tell me what conversion data I would need. 6. Add a second short query for a running cumulative total by hour, so I can see if I am ahead or behind last year at any point in the day. 7. Explain each part of the query in plain language and add comments inside the SQL. 8. Give me 3 quick sanity checks to compare against my store dashboard before I trust the numbers, and remind me to run it with a read only user. Deliverables: 1. Schema assumptions to confirm 2. Hour by hour comparison query 3. Cumulative total query 4. Plain language walkthrough 5. Sanity check list
How to use this prompt
- 1Copy the prompt using the button above.
- 2Open your AI tool (ChatGPT, Claude, Midjourney, etc.)
- 3Paste and customize any variables in the prompt.
- 4Generate your result.
Tip
Fill every placeholder before you run the prompt. Keep one clear goal per run, then refine with a follow-up if needed.
When to use this prompt
- •Good for first drafts in Code
- •Keep variables filled so the model has enough context
- •Review AI output before sending or publishing