DataLemur

Description

An interactive SQL & Analytics interview preparation platform founded by Nick Singh, a former Data Infrastructure Team member at Google Nest, SQL specialist at Facebook, and best-selling author of “Ace the Data Science Interview”.

‍

Project Background

With a solid background in SQL and data analytics, the client aimed to create an educational platform to help users improve their skills for employment at top companies like Facebook, Google, and Amazon. Additionally, it was crucial for the client to be actively involved in both the development and management processes — all within a limited budget and tight deadlines.

‍

Objectives

• Build an MVP as quickly as possible, utilizing accessible open-source and cost-effective solutions.
• Ensure the system could handle a large influx of users even in its MVP version.
• Create a complex yet affordable admin panel with a simple, intuitive UI, supporting content management, integration with OpenAI for content generation, and the ability to manage questions and dialects directly.
• Implement a system to track user achievements and monitor learning progress.
• Develop a lead management system, including email newsletters and custom metrics for lead generation.

‍

Tech Stack

Next.js + Next API Endpoints, Clerk, Render, Python, Node.js, Directus (BaaS) / Custom Directus Extensions, PostgreSQL, MySQL, ActiveCampaign, Senty, Stripe, AWS S3, Custom Markdown Parser, Ace editor, Zappier

‍

Key features

1. Onboarding
   
   • Registration
   • Authorization 
   • Referral
2. Rewards & engagement
3. Filters
   
   • Category
   • Status
   • Difficulty
   • Custom tags
4. Search
5. Homepage
   
   • Tasks list
   • Possibility to track your progress over different question categories (i.e. Easy, Medium, Hard)
   • Statistic section
6. Playground 
   
   • Code visualization
7. SQL and Python interactive questions database with possibility to run, check and submit your solution (see SQL question example and Python question example)
   
   • SQL Tutorial 
     
     1. A guide to succeed FAANG SQL interviews with sections divided by complexity
   • Solutions
   • Discussion with real people
   • Likes
8. Referral program
9. Point system 
   
   • Coupons
10. Public Blog
11. Lead management system
    
    1. User journey tracking with analytic tools
    2. Transactional and marketing emails service integration

‍

Key Solutions

• Implemented Access Levels and Subscriptions: ‍
  
  • We established different access levels within the platform, including subscriptions and payments, allowing for premium users.

‍

• Utilized Next.js for SEO Optimization: ‍
  
  • We built the site using Next.js to enhance SEO optimization. The client initially created a few posts himself and then generated additional content using GPT and DALL·E. We developed a script for automatic content uploading into Directus, enabling scheduled updates on the site, which resulted in excellent SEO performance thanks to Next.js.

‍

• Chose Render for Deployment: ‍
  
  • We chose Render for deployment due to its convenience and database management service. It automatically scales instances during high traffic, sends notifications for any issues, provides load graphs, offers metrics, and manages logging—all of which make it a better option than Heroku.

‍

• Implemented Python Execution in the Frontend Using Pyodide: ‍
  
  • To minimize infrastructure requirements and development time, we devised a solution to run Python code directly in the frontend. We used Pyodide, which translates Python code into JavaScript, and executes it within Web Workers to avoid blocking the main browser thread. This ensures users don't experience lag during Python code execution. The library supports all packages like NumPy and Pandas. Everything is integrated with Directus—we set test cases there, compare the correct query with the user's query, and provide feedback by comparing outputs.

‍

• Replaced Auth0 with Clerk for Authentication:‍
  
  • Initially, we implemented Auth0 for authentication, but over time, it became clear that it wasn't the most cost-effective solution for the business due to rising costs and integration complexities—reaching up to $2,000 for 100,000 users during peak times. At the client's request, we explored alternatives and decided on Clerk. Here’s how we managed the transition:
  • Exported all users from the previous solution.
  • Imported them into Clerk with zero downtime.
  • Adapted the database by exporting it as a JSON file, reformatting it to fit Clerk's API, and uploading it into Clerk.
  • Integrated with Next.js: We utilized Clerk's library for Next.js and developed synchronization and processing logic.
  • Minimal user disruption: The only issue users encountered was being logged out, but all their data remained intact. Since there were no ready-made migration solutions, we handled the entire process ourselves.

‍

• Developed Custom Modules for Directus.
  
  • ‍We chose Directus because it offers a more dynamic solution (Directus lets us define data schemas without coding via an intuitive interface, offers a customizable admin panel, and automatically generates REST and GraphQL APIs. This flexibility enables rapid development and easy adaptation to user feedback and requirements.). However, Directus alone didn't meet all our initial requirements, so we developed numerous custom modules based on user and business needs. These modules included integrations with OpenAI (for blog content), Stripe (payments), and ActiveCampaign (email newsletters). When a user is created, they're assigned a referral code and added to ActiveCampaign, among other actions.
  • Directus allows the business to manage content and databases through a convenient and customizable UI without the need for custom-built admin panels.
    
    

• Implemented MySQL and Replication Converter: ‍
  
  • We introduced MySQL by adding a new runtime. To achieve this, we added functionality to Directus using TypeScript: a converter from PostgreSQL to MySQL dialect to automatically replicate data tables.

‍

• Set Up a Separate Python Instance with FastAPI: ‍
  
  • We created a separate Python instance where, through a Next.js API route, we send requests to a Python server running FastAPI. The server executes the code and returns the results to the Next.js API route, which then delivers the output to the user.