stratascratch
  • Interview Questions
    • Interview Loops
    • Analytical
    • Algorithm
    • ML Coding
    • Stats Coding
    • Visualization
    • Concept
  • Data Labs
    • Discover
    • Your Notebooks
  • Mock Interview
  • Learn
  • Blog
  • Pricing
  • About
Log in
  • Interview Questions

    • Interview Loops
    • Analytical
    • Algorithm
    • ML Coding
    • Stats Coding
    • Visualization
    • Concept
  • Data Labs

    • Discover
    • Your Notebooks
  • Mock Interview
  • Learn
  • Blog
  • Pricing
  • About
Log inSign up
Back to Projects
  1. Assignment
  2. Your Solution
  3. Solution
  4. Solutions from Users
  5. Discussion

Data Transformation

You must be logged in to download the datasets

Instacart: Flatten event payloads into analytics-ready tables via ETL pipelines. Python, Pandas, JSON.

Instacart is a modern, app-first company, with an iOS App and an Android App. To grow our company we must make a special effort in collecting all information available around the App and make it available for everyone in the company. Some information about App usage is generated by our App and our backend API. Other sources of information, like the Google Play Store, can provide very useful insights on App performance and user rating. Instacart is very data-oriented and, most decisions are made based on appropriate metrics, therefore, data quality is a must and a concern of everyone involved.

Assignment

Some event data come as json files and need some transformation to be structured as tables. Convert the case.json file to 3 csv files, using the programming language of your choice, with the following rules:

  1. CuratedOfferOptions.csv:
CurationProvider: in quotes
OfferId: in quotes
DealerId: in quotes
UniqueOptionId: in quotes
OptionId: in quotes
IsMobileDealer: without quotes
IsOpen: without quotes
Eta: in quotes
ChamaScore: without quotes
ProductBrand: in quotes
IsWinner: without quotes
MinimumPrice: without quotes
MaximumPrice: without quotes
DynamicPrice: without quotes
FinalPrice: without quotes
DefeatPrimaryReason: in quotes
DefeatReasons: in quotes
EnqueuedTimeSP: DD/MM/YYYY (converted to Brasilian timezone - UTC-3)
  1. DynamicPriceOption.csv:
Provider: in quotes
OfferId: in quotes
UniqueOptionId: in quotes
BestPrice: without quotes
EnqueuedTimeSP: DD/MM/YYYY (converted to Brasilian timezone - UTC-3)
  1. DynamicPriceRange.csv:
Provider: in quotes
OfferId: in quotes
MinGlobal: without quotes
MinRecommended: without quotes
MaxRecommended: without quotes
DifferenceMinRecommendMinTheory: without quotes
EnqueuedTimeSP: DD/MM/YYYY (converted to Brasilian timezone - UTC-3)

Data Description

You are given one file case.json. Below is an example of a single record from this file:

{
    "EnqueuedTimeUtc": "2021-09-05 08:04:08 UTC",
    "EventName": "DynamicPrice_Result",
    "Payload": "{\"provider\":\"ApplyDynamicPriceRange\",\"offerId\":\"a6611d55-9624-4381-8cdd-323ee3689241\",\"algorithmOutput\":{\"min_global\":85.0,\"min_recommended\":87.2,\"max_recommended\":97.65,\"differenceMinRecommendMinTheory\":2.2}}"
  }

Practicalities

Make sure that the solution reflects your entire thought process - it is more important how the code is structured rather than the final CSV files.

This question is part of an interview loop

See the full interview round by round, with questions for each stage.

E-commerce & retail · Analytics Engineer