Create an sqlite db of your leetcode visits and submissions to track progress

leetcode_etl.rb

# Script runs on mac. Would need to be adapted to run on windows.

require 'sqlite3'
require 'json'
require 'zip'
require 'tmpdir'
require 'find'

# Function to unzip the file
def unzip_file(file, destination)
  Zip::File.open(file) do |zip_file|
    zip_file.each do |entry|
      entry.extract(File.join(destination, entry.name))
    end
  end
end
`rm submissions.db`
# Initialize SQLite database
db = SQLite3::Database.new "submissions.db"

db.execute <<-SQL
  CREATE TABLE submissions (
    id INTEGER PRIMARY KEY,
    submission_id INTEGER,
    timestamp INTEGER,
    problem_name TEXT,
    lang TEXT,
    runtime TEXT,
    memory TEXT,
    status TEXT
  );
SQL


db.execute <<-SQL
  CREATE TABLE visits (
    id INTEGER PRIMARY KEY,
    problem_name TEXT,
    visit_time INTEGER
  );
SQL


# Path to the copy of the Chrome history file (note this is Mac specific)
chrome_db_file_path = File.expand_path('~/Library/Application Support/Google/Chrome/Default/History')

# Connect to SQLite database
chrome_db = SQLite3::Database.new(chrome_db_file_path)

# Query to fetch URLs and last_visit_times
query = "SELECT urls.url, visit_time  / 1000000 + (strftime('%s', '1601-01-01')) as visit_time from visits join urls on visits.url = urls.id where urls.url like '%leetcode.com/problems/%';"

# Execute the query
chrome_db.execute(query) do |row|
  db.execute("INSERT INTO visits (problem_name, visit_time) VALUES (?, ?)", [row[0][/leetcode.com\/problems\/([\w\-]+)/, 1], row[1]])
end

# Close the database
chrome_db.close

# Follow the instructions here https://github.com/world177/Leetcode-Downloader-for-Submissions and save the file to downloads
zip_file_path = File.expand_path("~/Downloads/submissions.zip")

Dir.mktmpdir do |unzip_destination|
  unzip_file(zip_file_path, unzip_destination)

  # Recursively search for 'info.txt' files in all subfolders
  Find.find(unzip_destination) do |path|
    if File.basename(path) == 'info.txt'
      # Read the info.txt file and parse the JSON
      info_content = File.read(path)
      info_json = JSON.parse(info_content)

      # Extract the problem name from the directory structure
      problem_name = File.dirname(path).split('/')[-3]

      # Insert into SQLite DB
      db.execute("INSERT INTO submissions (submission_id, timestamp, problem_name, lang, runtime, memory, status) VALUES (?, ?, ?, ?, ?, ?, ?)",
                 [info_json['id'], info_json['timestamp'], problem_name, info_json['lang'], info_json['runtime'], info_json['memory'], info_json['status'] == 10 ? 'Accepted' : 'Rejected'])
    end
  end
end

db.close

leetcode_submission_durations.sql

SELECT
    s.problem_name, 
    DATE(v.visit_time, 'unixepoch') as day,
    min(v.visit_time) as visit_time,
    min(s.timestamp) as submission_time,
    min(s.timestamp) - min(v.visit_time) as diff_s
FROM submissions s
JOIN visits v ON s.problem_name = v.problem_name
WHERE s.status = 'Accepted' and timestamp > visit_time
group by 1,2;