def get_test_size_stats():
"""
Fetches the distribution of test sizes chosen by users, and the completion
statistics for each reported size.
"""
cursor = connection.cursor()
cursor.execute("""
SELECT n_items, COUNT(*), AVG(is_finished) FROM (
SELECT
ts.id AS test_set_id,
COUNT(*) AS n_items,
(ts.end_time IS NOT NULL) AS is_finished
FROM drill_testset_questions AS tsq
INNER JOIN drill_testset AS ts
ON tsq.testset_id = ts.id
GROUP BY ts.id
) as tmp
GROUP BY n_items
ORDER BY n_items ASC
""")
data = cursor.fetchall()
dist = FreqDist()
completion_rates = {}
for n_items, n_tests, completion_rate in data:
dist.inc(n_items, n_tests)
completion_rates[n_items] = completion_rate
results = []
for sample in sorted(dist.samples()):
results.append((sample, dist.freq(sample), completion_rates[sample]))
return results
def get_test_size_stats():
"""
Fetches the distribution of test sizes chosen by users, and the completion
statistics for each reported size.
"""
cursor = connection.cursor()
cursor.execute("""
SELECT n_items, COUNT(*), AVG(is_finished) FROM (
SELECT
ts.id AS test_set_id,
COUNT(*) AS n_items,
(ts.end_time IS NOT NULL) AS is_finished
FROM drill_testset_questions AS tsq
INNER JOIN drill_testset AS ts
ON tsq.testset_id = ts.id
GROUP BY ts.id
) as tmp
GROUP BY n_items
ORDER BY n_items ASC
""")
data = cursor.fetchall()
dist = FreqDist()
completion_rates = {}
for n_items, n_tests, completion_rate in data:
dist.inc(n_items, n_tests)
completion_rates[n_items] = completion_rate
results = []
for sample in sorted(dist.samples()):
results.append((sample, dist.freq(sample), completion_rates[sample]))
return results
def get_accuracy_by_pivot_type():
cursor = connection.cursor()
cursor.execute("""
SELECT
question.pivot,
SUM(chosen_option.is_correct) as n_correct,
COUNT(*) as n_responses
FROM (
SELECT mco.question_id, mco.is_correct
FROM drill_multiplechoiceresponse AS mcr
INNER JOIN drill_multiplechoiceoption AS mco
ON mcr.option_id = mco.id
) as chosen_option
INNER JOIN drill_question AS question
ON chosen_option.question_id = question.id
WHERE question.pivot_type = "w"
GROUP BY question.pivot
""")
raw_data = cursor.fetchall()
counts = {
'Hiragana': FreqDist(),
'Katakana': FreqDist(),
'Kanji': FreqDist()
}
complex_scripts = set([scripts.Script.Kanji, scripts.Script.Unknown])
only_katakana = set([scripts.Script.Katakana])
for word, n_correct, n_responses in raw_data:
scripts_found = scripts.script_types(word)
if scripts_found.intersection(complex_scripts):
dist = counts['Kanji']
elif scripts_found.intersection(only_katakana):
dist = counts['Katakana']
else:
dist = counts['Hiragana']
dist.inc(True, int(n_correct))
dist.inc(False, int(n_responses - n_correct))
keys = ('Hiragana', 'Katakana', 'Kanji')
data = [(key, counts[key].freq(True)) for key in keys]
average = FreqDist()
for key in keys:
average.inc(True, counts[key][True])
average.inc(False, counts[key][False])
data.append(('Average', average.freq(True)))
return data
def get_accuracy_by_pivot_type():
cursor = connection.cursor()
cursor.execute("""
SELECT
question.pivot,
SUM(chosen_option.is_correct) as n_correct,
COUNT(*) as n_responses
FROM (
SELECT mco.question_id, mco.is_correct
FROM drill_multiplechoiceresponse AS mcr
INNER JOIN drill_multiplechoiceoption AS mco
ON mcr.option_id = mco.id
) as chosen_option
INNER JOIN drill_question AS question
ON chosen_option.question_id = question.id
WHERE question.pivot_type = "w"
GROUP BY question.pivot
""")
raw_data = cursor.fetchall()
counts = {'Hiragana': FreqDist(), 'Katakana': FreqDist(), 'Kanji':
FreqDist()}
complex_scripts = set([scripts.Script.Kanji, scripts.Script.Unknown])
only_katakana = set([scripts.Script.Katakana])
for word, n_correct, n_responses in raw_data:
scripts_found = scripts.script_types(word)
if scripts_found.intersection(complex_scripts):
dist = counts['Kanji']
elif scripts_found.intersection(only_katakana):
dist = counts['Katakana']
else:
dist = counts['Hiragana']
dist.inc(True, int(n_correct))
dist.inc(False, int(n_responses - n_correct))
keys = ('Hiragana', 'Katakana', 'Kanji')
data = [(key, counts[key].freq(True)) for key in keys]
average = FreqDist()
for key in keys:
average.inc(True, counts[key][True])
average.inc(False, counts[key][False])
data.append(('Average', average.freq(True)))
return data
