Data Interview Qs - Moving average trading strategy


Moving average trading strategy

SQL Database Querying Window Function Subquery

Forward this email to a friend or colleague and challenge them to solve it.


Suppose you're given the following table showing open, high, low, and close prices for a stock on a given date (preview of the schema, full schema/table in SQL fiddle link below):

Table: Prices

Date Open High Low Close
2020-01-02 00:00:00 1875 1898.01 1864.15 1898.01
2020-01-03 00:00:00 1864.5 1886.2 1864.5 1874.97
2020-01-06 00:00:00 1860 1903.69 1860 1902.88
2020-01-07 00:00:00 1904.5 1913.89 1892.04 1906.86

Given this data, you're asked to apply the following trading strategy:

  • Calculate the 10-day moving average of the closing price
  • Then, if the price of the day's open is greater than the 10-day moving average, you will "buy" the stock on open and sell on close for that day.

Write a SQL query to apply this trading strategy and return the total earnings/loss over the period of data provided.

You can view/query the table in an interactive SQL fiddle here.


You’ve accumulated 369 questions since joining our email list. Create a premium account to access detailed solutions to each problem (including this one) + our new Case studies.

Was this email forwarded to you? You can join our list here to receive questions

How did you like today's question?

15411 Boischatel, Montreal, QC, H9H1Y7, Canada
Don't want to get these emails anymore? We'll forgive you if you unsubscribe

Older messages

Selecting computers for a company

Monday, November 7, 2022

Data Analysis, Python, Pandas, Data Manipulation, Data Visualization, External Dataset, Case Study, InterviewQs Selecting computers for a company Data Analysis Python Pandas Data Manipulation Data

Matrix multiplication using nested loops

Friday, November 4, 2022

Python, Data Structures, Algorithms, Matrix Multiplication, InterviewQs Matrix multiplication using nested loops Python Data Structures Algorithms Matrix Multiplication Forward this email to a friend

Evaluating lottery system

Wednesday, November 2, 2022

Statistics, Hypergeometric Distribution, Probability, InterviewQs Evaluating lottery system Statistics Hypergeometric Distribution Probability Forward this email to a friend or colleague and challenge

Highest performing students by college program

Monday, October 31, 2022

SQL, Database Querying, Subquery, Case Statement, InterviewQs Highest performing students by college program SQL Database Querying Subquery Case Statement Forward this email to a friend or colleague

Multicollinearity in linear regression

Friday, October 28, 2022

Statistics, Regression, Multicollinearity, PCA, InterviewQs Multicollinearity in linear regression Statistics Regression Multicollinearity PCA Forward this email to a friend or colleague and challenge

SRE Weekly Issue #350

Monday, December 5, 2022

View on A message from our sponsor, Rootly: Manage incidents directly from Slack with Rootly 🚒. Rootly automates manual tasks like creating an incident channel, Jira ticket and Zoom rooms

WP Weekly 125 - Together - Supporting WP, LTD Ending, Your WP Feedback

Monday, December 5, 2022

Read on Website WP Weekly 125 / Together It is so cool when community members come together to fill gaps like with initiatives of community collective, certifyWP, and WP gives a hand. Also featured in

Weekend Reading — “There ain't no more swaps”

Sunday, December 4, 2022

This week we got two serverless databases, 8-bit icons, a better search experience, follow links for Mastodon, and a 3D printer challenge. 😎 Labnotes (by Assaf Arkin) Weekend Reading — “There ain't

Building a Virtual Machine Inside ChatGPT — Apple to move 40-45% iPhone production to India — and The War on General Purpose Computing (2015) [video]

Sunday, December 4, 2022

Issue #971 — Top 20 stories of December 05, 2022 Issue #971 — December 05, 2022 You receive this email because you are subscribed to Hacker News Digest. You can open it in the browser if you prefer. 1

Software Testing Weekly - Issue 148

Sunday, December 4, 2022

Why Google doesn't have a QA department 🤔 View on the Web Archives ISSUE 148 December 4th 2022 COMMENT Welcome to the 148th issue! Hope you've had a great weekend and you're ready to find

#385 Is React going anywhere?

Sunday, December 4, 2022

#385 – December 05, 2022 View in browser React Digest Is React going anywhere? Earlier this year I had an interesting conversation with a CTO of a price comparison website (e-commerce) and he mentioned

#433 Processing data in parallel using Channels

Sunday, December 4, 2022

#433 – December 05, 2022 View in browser C# Digest Processing data in parallel using Channels Thanks to the Task Asynchronous Programming model writing asynchronous code in .NET is usually

☁️ Azure Weekly #398 - 4th December 2022

Sunday, December 4, 2022

Azure Weekly Newsletter Issue #398 powered by endjin Welcome to issue 398 of the Azure Weekly Newsletter. There's a bit of a melting pot of news and articles this week. Items of note around

What does it mean to be resilient & The office re-imagined

Sunday, December 4, 2022

In this week's issue of Creativerly: Maximise your Mac performance, draw with delight, how to become comfortable with uncertainty, and a lot more. Creativerly Creativerly What does it mean to be

#118 Company, team, self

Sunday, December 4, 2022

#118 – December 05, 2022 View in browser Tech Lead Digest Company, team, self Back when I was managing at Uber, I latched onto a thinking tool that I drilled into the teams I worked with: reach the