Tally how often each template's invocations are edited.
Here's a working query to rank templates according to how often the TemplateWizard is used to edit an invocation:
popular_templates = spark.sql("""
with templates_used as (
select
wiki,
explode(event.template_names) as template_name
from event.templatewizard
where
event.action = 'save-page'
and year = 2020
and array_contains(split('{}', ' '), wiki)
),
template_frequency as (
select
wiki,
count(1) as frequency,
template_name
from templates_used
group by wiki, template_name
),
template_frequency_ranked as (
select
wiki,
row_number() over (partition by wiki order by frequency desc) as rank,
frequency,
template_name
from template_frequency
)
select
wiki, frequency, template_name
from template_frequency_ranked
where
rank <= 10
""".format(
" ".join(wikis)
))
popular_templates.show()Some results:
+------+---------+--------------------+ | wiki|frequency| template_name| +------+---------+--------------------+ |enwiki| 257| Cite web| |enwiki| 207| Infobox person| |enwiki| 133| Cite news| |enwiki| 112| Reflist| |enwiki| 75| Cite book| |enwiki| 74| Reply to| |enwiki| 67| Citation needed| |enwiki| 63| Infobox television| |enwiki| 59| Infobox company| |enwiki| 49|WikiProject Biogr...| |fawiki| 14| صفحه بحث| |fawiki| 12| یادکرد وب| |fawiki| 8| ساعت عقربهای| |fawiki| 7| Infobox person| |fawiki| 7| ساعت رسمی ایران| |fawiki| 6| جعبه کاربر/بازنشسته| |fawiki| 6| رنگ پسزمینه| |fawiki| 6| مشارکت کاربر| |fawiki| 6| Color| |fawiki| 5| هشدار| +------+---------+--------------------+