Fitness | Fellowship | Faith®

PAXminer Queries

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.

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

 

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 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 PAX, AO, Date AS Last_Post
    FROM attendance_view av
    WHERE Date < NOW() - INTERVAL 30 DAY
    AND Date = (SELECT MAX(t2.Date)
                 FROM attendance_view t2
                 WHERE t2.PAX = av.PAX)
    GROUP BY PAX) 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
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