This page has example queries you can use for pulling data from PAXminer.
Copy and paste the red queries directly into a new SQL tab in your database tool (e.g. DBeaver, MySQL Workbench, or DbVisualizer) and click “Play”. The query results will appear as a table similar to the examples shown.
How to run a query
- In DBeaver, click “New SQL Editor”
- In the SQL window, paste the SQL query you want to run
- Click play
- Results show up in the bottom table. you can export / copy into excel or whatever you need.
Region total PAX, Posts, and Beatdowns by Month + Year
This query will pull region summaries by month / year to detail how many unique PAX have been active in a region by month (to see region growth) as well as the total numbers of beatdowns held, and attendance statistics.
SELECT YEAR(Date) AS Year,
MONTH(Date) as Month,
sum(pax_count) AS Total_Pax,
count(AO) AS Beatdowns,
ROUND(SUM(pax_count)/count(AO),1) as AveragePerAO,
SUM(fng_count) as FNGs
FROM beatdown_info
GROUP BY Year, Month
ORDER BY Year desc, Month Desc
AO total PAX, Posts, and Beatdowns by Week # + Year
This query will pull AO specific summaries by week number / year to detail how many unique PAX have been active at an AO by week as well as the total numbers of beatdowns held, and attendance statistics.
SELECT AO, YEAR(Date) AS Year,
WEEK(Date) as Week,
sum(pax_count) AS Total_Pax,
count(AO) AS Beatdowns,
ROUND(SUM(pax_count)/count(AO),1) as AveragePerAO,
SUM(fng_count) as FNGs
FROM beatdown_info
GROUP BY AO, Year, Week
ORDER BY AO, Year desc, Week Desc
Region - All PAX with total # of posts each
This query will pull your entire roster: paxname, real name, phone, email, and total number of posts.
SELECT u.user_name, u.real_name AS BirthName, u.email AS Email, u.phone AS Phone, t1.AOs_2021, t1.Posts_2021
FROM users u
INNER JOIN (
SELECT PAX, count(distinct AO) as AOs_2021, count(Date) as Posts_2021
FROM attendance_view
WHERE YEAR(Date) = 2021
GROUP BY PAX
) t1
ON u.user_name = t1.PAX
ORDER BY PAX 
Kotters Query
This query is to help 2nd-F Qs and Site-Qs to identify any PAX that may need someone to reach out to them. Are they doing OK? The query returns all PAX that have not attended a beatdown in >30 days and includes additional information including the PAX name, email address, how many times in total the PAX has attended a beatdown, which AO they attend the most, and the date of their last attended beatdown. You can change the date # for any date range you want.
SELECT DISTINCT U.user_name AS PAX, U.real_name AS Real_Name, U.phone AS Phone, U.email AS Email, latest.AO, latest.Last_Post, total.Total_Posts, p3.Home_AO, p3.Home_AO_Posts
FROM users U
INNER JOIN (
SELECT t.PAX, t.AO, t.Date as Last_Post
FROM attendance_view t
INNER JOIN (
SELECT PAX, MAX(Date) AS MaxDate
FROM attendance_view av
WHERE av.DATE > NOW() - INTERVAL 365 DAY
GROUP BY PAX) tm ON t.PAX = tm.PAX AND t.Date = tm.MaxDate) AS latest
ON U.user_name = latest.PAX
INNER JOIN (
SELECT PAX, COUNT(*) AS Total_Posts
FROM attendance_view
GROUP BY PAX) as total
ON latest.PAX = total.PAX
INNER JOIN (
SELECT p2.PAX, p2.Home_AO, p2.Home_AO_Posts
FROM (
SELECT PAX, Home_AO, Home_AO_Posts, ROW_NUMBER()
OVER(PARTITION BY PAX ORDER BY Home_AO_Posts DESC) rowNumber
FROM (
SELECT PAX, AO as Home_AO, COUNT(PAX) as Home_AO_Posts
FROM attendance_view
GROUP BY PAX, Home_AO) pp) p2
WHERE p2.rowNumber = 1) as p3
ON p3.PAX = latest.PAX
WHERE Last_Post < NOW() - INTERVAL 30 DAY
ORDER BY Home_AO, Home_AO_Posts Desc 
Q Counts by PAX per AO
This query will provide a count of who has Q’d and how many times for EACH AO for the year (note, year is shown as 2021).
SELECT Q,
AO, count(Q) AS Qd_Beatdowns
FROM beatdown_info
WHERE YEAR(Date) = 2021
GROUP BY Q, AO
ORDER BY AO, Q 
Unique AO Attendance per PAX in a specific month
This query shows how many unique AOs each PAX has attended in a given month. This is helpful if you’re doing an AO challenge in a month to see who can hit up the most AOs.
Note: Use the month #, the example below would return counts for October, 2021. Also, in order to exclude non-AO things like QSource and Black ops, this query only looks for AOs that start with “ao” as that is how some regions denote actual AOs. You may need to modify this as needed, depending on how you name your AOs.
SELECT PAX, COUNT(*) AS AOs
FROM (
SELECT DISTINCT PAX, AO
FROM attendance_view
WHERE MONTH(Date) = 10
AND YEAR(Date) = 2021
AND AO LIKE "ao%"
) Count
GROUP BY PAX
ORDER BY AOs desc, PAX
Busiest Days of the Week by AO across the year
This query shows which day of the week is the most highly attended (or least attended) on average for each AO. Useful when looking to modify AO calendars or identify which days a new AO should schedule beatdowns.
SELECT AO,
YEAR(Date) AS Year,
DAYNAME(Date) AS DayOfWeek,
sum(pax_count) AS Total_Pax,
count(AO) AS Beatdowns,
ROUND(SUM(pax_count)/count(AO),1) as AveragePerAO,
SUM(fng_count) as FNGs
FROM beatdown_info
GROUP BY AO, Year, DayOfWeek
ORDER BY AO, Year desc, AveragePerAO Desc 
Housekeeping: Delete a specific backblast entry
This creates a “stored procedure” for deleting bad backblasts. Run this ONE TIME, and it will save the procedure in your database. Then, to use it – all you have to do is run the “CALL” statement and change the date/AO/Q in it. It will delete the correct rows from your beatdowns and bd_attendance tables:
DELIMITER //CREATE PROCEDURE delete_beatdown(IN input_date DATE, input_ao VARCHAR(45), input_q VARCHAR(45))BEGIN/* Note: This procedure is a stored SQL statement that will delete backblast records AND PAX attendance for a specified date, Q, and AO.To run this procedure, execute the following statement (update it to use the correct date, AO channel, and Q as appropriate):CALL delete_beatdown('yyyy-mm-dd', 'AO slack channel name', 'Q name');*/DELETEFROM beatdownsWHERE ao_id = (SELECT channel_idFROM aosWHERE ao = input_ao)AND bd_date = input_dateAND q_user_id = (SELECT user_idFROM usersWHERE user_name = input_q);
DELETE FROM bd_attendance WHERE ao_id = ( SELECT channel_id FROM aos WHERE ao = input_ao) AND date = input_date AND q_user_id = ( SELECT user_id FROM users WHERE user_name = input_q);END // DELIMITER ;
After you run that, it will make a callable procedure for you to use in the future. So for instance, if you needed to delete a bad backblast YOU entered for today at aobigez, it would say:CALL delete_beatdown('2022-01-20, 'aobigez', 'DJ Vespa');
Stapler's Crummy Query to fine duplicate backlists with changed dates
This query finds any bad backblast entries where a Q may have had an incorrect date that was entered, and then they corrected the BB with a different date that was imported again.
selectAO, Q, CoQ, pax_count, fngs, fng_count, left(backblast,20) as Left15, count(0), max(date), min(date)from backblast
where Q <> 'PAXminer'
group by AO, Q, CoQ, pax_count, fngs, fng_count, left(backblast,20)having count(0) > 1
order by count(0) desc
Stapler's Query to find missing backlists
This query finds any bad backblast entries where a Q may have had an incorrect date that was entered, and then they corrected the BB with a different date that was imported again.
set @startdate = '2021-01-01';
set @AO = 'ao-thestride';
set @DOW = '1';select
AO, d.bd_date, Q, case when bd.AO is null then 'Missing BB' else 'BB Done' end as BBCheck, bd.pax_count, bd.FNGs, bd.FNG_count
from (select
bd_date, dayofweek(bd_date) as DOW
from beatdowns
group by bd_date, dayofweek(bd_date)) d
left outer join (
select
AO, date, dayofweek(date) as DOW, pax_count, FNGs, FNG_count, Q
from beatdown_info
where AO = @AO) bd on bd.date = d.bd_date
where d.DOW = @DOW and bd_date >= @startdate
order by d.bd_date desc
Milestone Query to identify PAX near a x00 post milestone
Giggles (F3 Naperville) wrote this Milestone query to help identify those near a x00 milestone (ie 100, 200, 300, etc Posts) that have been active in the last 180 day
