// ==UserScript==
// @name        Perlmonks Saints in our Book to Oracle
// @namespace   http://www.example.com/chacham/
// @description Turn the data imn Perlmonks Saints in our Book into an Oracle quey with CTEs
// @include     http://perlmonks.org/?node_id=3559
// @version     1
// @grant       none
// ==/UserScript==

var query = "WITH\n\tData(Ranking, Name, XP, Writeups)\nAS\n\t(";
var table = document.getElementsByTagName('table')[4];

for(var row = 1; row < table.rows.length; row++)
 query += "\n\t SELECT "
  + table.rows[row].cells[0].textContent + ",\t'"
  + table.rows[row].cells[1].textContent + "',\t"
  + table.rows[row].cells[2].textContent + ",\t"
  + table.rows[row].cells[4].textContent.replace('None', 0) + "\t FROM Dual UNION ALL";

// Chop off the extra UNION ALL
query = query.slice(0, -10)
+ "\n\t),\n\tStep_XP_Writeups(Ranking, Name, XP, Writeups, XP_Diff, Writeups_Diff) \
\nAS \
\n\t( \
\n\t SELECT \
\n\t\tRanking, \
\n\t\tName, \
\n\t\tXP, \
\n\t\tWriteups, \
\n\t\tXP - LEAD(XP) OVER(ORDER BY Ranking), \
\n\t\tWriteups - LEAD(Writeups) OVER(ORDER BY Ranking) \
\n\t FROM \
\n\t\tData \
\n), \
\n\tClose(Ranking, XP_Diff, Writeups_Diff) \
\nAS \
\n\t( \
\n\t SELECT \
\n\t\tRanking, \
\n\t\tXP - LEAD(XP) OVER(ORDER BY Ranking), \
\n\t\tWriteups - LEAD(Writeups) OVER(ORDER BY Ranking) \
\n\t FROM \
\n\t\tStep_XP_Writeups \
\n\t WHERE \
\n\t\tXP_Diff\tBETWEEN -30 AND 30 \
\n\t  AND\tWriteups_Diff\tBETWEEN -5 AND 5 \
\n), \
\n\tMMA(XP_Diff_Min, XP_Diff_Max, XP_Diff_Avg, Writeups_Diff_Min, Writeups_Diff_Max, Writeups_Diff_Avg)\
\nAS \
\n\t( \
\n\t SELECT \
\n\t\tMIN(XP_Diff), \
\n\t\tMAX(XP_Diff), \
\n\t\tAVG(XP_Diff), \
\n\t\tMIN(Writeups_Diff), \
\n\t\tMAX(Writeups_Diff), \
\n\t\tAVG(Writeups_Diff) \
\n\t FROM \
\n\t\tStep_XP_Writeups \
\n\t), \
\n\tStats(Both, Close, XP, XP_Diff_Min, XP_Diff_Max, XP_Diff_Avg, Writeups, Writeups_Diff_Min, Writeups_Diff_Max, Writeups_Diff_Avg) \
\nAS \
\n\t( \
\n\t SELECT \
    \n\t(SELECT COUNT(COUNT(*)) FROM Data GROUP BY XP, Writeups    HAVING Count(*) > 1), \
    \n\t(SELECT COUNT(*) FROM Close), \
    \n\t(SELECT COUNT(COUNT(*)) FROM Data GROUP BY XP HAVING Count(*) > 1), \
    \n\tXP_Diff_Min, \
    \n\tXP_Diff_Max, \
    \n\tXP_Diff_Avg, \
    \n\t(SELECT COUNT(COUNT(*)) FROM Data GROUP BY Writeups HAVING Count(*) > 1) Writeups, \
    \n\tWriteups_Diff_Min, \
    \n\tWriteups_Diff_Max, \
    \n\tWriteups_Diff_Avg \
\n FROM \
\n\tMMA\n\t) \
\n--SELECT Both, Close, XP, XP_Diff_Min, XP_Diff_Max, XP_Diff_Avg, Writeups, Writeups_Diff_Min, Writeups_Diff_Max, Writeups_Diff_Avg FROM Stats \
\nSELECT \
\n\tData.Ranking, \
\n\tData.Name, \
\n\tData.XP, \
\n\tData.Writeups \
\nFROM \
\n\tData, \n\tClose \
\nWHERE \
\n\tData.Ranking BETWEEN Close.Ranking AND Close.Ranking + 1 \
\nORDER BY \
\n\tData.Ranking;";


var textarea = document.createElement('textarea');
textarea.cols = 100;
textarea.rows = 20;
textarea.value = query;

document.body.appendChild(textarea);