Dataford
Popular roles
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Browse
Browse All RolesEvery role hub, from analyst to MLBrowse All CompaniesCompany-specific interview loopsAll Interview GuidesThe full guide library
Top questions by role
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Top questions by skill
SQLPythonStatisticsMachine LearningA/B TestingSystem DesignGenerative AIProduct SenseMetricsBehavioral
Browse all questions →Try a mock interview
ExperiencesCoaching
New
Practice
Mock InterviewsTimed interview simulations with feedbackSuccess PathYour 6-week structured planModulesCurated lessons by topicWebinarsTalks from ex-Big Tech data leadsPlaygroundA free-form scratch editor
Learn
BlogInterview strategy and career adviceTech Job Market ReportHiring trends across data and AI rolesFor UniversitiesDataford for career centersAbout DatafordWho we are and how we build
Pricing
Get started
SQL Price Increase Each Month
00:00
5 left

SQL Price Increase Each Month

HardSQL · PostgreSQL

Problem

Amazon's product analytics team wants to identify catalog items whose latest monthly price increased consistently. Assume the reporting date is 2024-06-01, so the three complete months are March, April, and May 2024. Write a PostgreSQL query that returns products with a strictly higher month-end price in each successive month.

Requirements

  1. Select the latest recorded price for each product in each of the three months.
  2. Exclude products without exactly one qualifying monthly price for March, April, and May.
  3. Return products whose March price is less than April's price and April's price is less than May's price.
  4. Return the product ID, product name, and the three monthly prices, ordered by product ID.

Output details

  • Return columns named product_id, product_name, march_price, april_price, and may_price, in that order.
  • Order results by product_id ascending.
  • When timestamps tie for a product-month, select the row with the greatest price_history_id.

Schema

products
ColumnTypeDescription
product_idPKINTUnique product identifier
product_nameVARCHAR(100)Product name in the catalog
product_price_history
ColumnTypeDescription
price_history_idPKINTUnique price history record identifier
product_idINTReferences products.product_id
recorded_atTIMESTAMPTimestamp when the price was recorded
priceNUMERIC(10,2)Recorded product price in US dollars
Tablesproductsproduct_price_history
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the two tables are in the Question tab.

You need to log in / sign up to run or submit.
CodePostgreSQL
Sign up free to run your codeLog inLn 1
Run your query to see results here.