Combine 2 SQLite result sets to calculate percentage

Question

I have a single sqlite table that I'm trying to pull two resultsets from and then calculate the percentage. Table columns:

• email
• Location
• Clicked?
• Test#

Test# 123 has a list of users (identified by email address) who clicked a certain link, Test# 456 includes the same people who clicked in test#123, but with only a subset of the users from Test# 123 clicking a second time. The goal is to pull the percentage of those who clicked a second time in Test#456.

Query 1: (dividend) to calculate those who clicked a second time is:

SELECT Location, count(*) as "rClicks" from table
WHERE Test# = "456" and "Clicked?" = "Yes" and email IN (
    SELECT email from table
    WHERE test# = "123" and "Clicked?" = "Yes")
GROUP BY Location

Query 2: (divisor) to calculate the total who clicked on the first test is:

SELECT Location, count(*) as "Clicks" from table
WHERE test# = "123" and "Clicked?" = "Yes"
GROUP BY Location

I've tried all sorts of combinations, the most promising being an INNER JOIN on the two result sets, but I keep getting 0.0 for the percentage.

SELECT query1.Location, query1."rClicks" / query2."Clicks" * 100.0 as "Percentage"
FROM (query 1) repeats
INNER JOIN
(query 2) previous
ON repeats.Location = previous.Location

Any suggestions for getting this right?

Answer 1

A fellow redditor provided me with the assistance to answer my question. The following is the end result that did what I needed:

select t.location, printf("%.3f", sum(st.456Clicks))/printf("%.3f", sum(st.123Clicks)) as percentage
from (
    select email, count(*) as 123Clicks, 0 as 456Clicks
    from table
    where test = "123"
    group by email

    union all

    select email, 0 as 123Clicks, count(*) as 456Clicks
    from table
    where test = "456" and "Clicked?" = "Yes"
    group by email
) st
join table t
on t.email = st.email
where t."Clicked?" = "Yes" and t.test = "123"
group by t.location
order by percentage DESC