Create a stats table which shows the country distribution of orders

[malemburg]

Here's the SQL for the query:

-- Ticket distribution of orders for EP2020
select assopy_order.country_id, count(*) as count from assopy_order
left join assopy_orderitem on assopy_order.id = assopy_orderitem.order_id
where created > '2020-01-01' and created < '2020-12-31' and complete = 1 and assopy_orderitem.code like 'T__'
group by assopy_order.country_id
order by count desc;

Would be good to add this to the attendee stats page we already have.

[matrixise]

maybe we could use a SQL view and create a Django model over it.

[Tapan123456]

Do we only need to display this data over the UI or is it something else, I can work on it

[fpierfed]

I would like to have a page displaying these results. A table as suggested in the ticket would be great. A nice addition would be a map color coded depending on number of attendees.

[europython-society]

On 10.05.2021 09:52, Francesco Pierfederici wrote: I would like to have a page displaying these results. A table as suggested in the ticket would be great. A nice addition would be a map color coded depending on number of attendees.

Here's the SQL query I've been using for this: ``` -- Ticket distribution of orders for EP2021 select assopy_order.country_id, count(*) as count from assopy_order left join assopy_orderitem on assopy_order.id = assopy_orderitem.order_id left join conference_ticket on assopy_orderitem.ticket_id = conference_ticket.id where created > '2021-01-01' and created < '2021-12-31' and _complete = 1 and assopy_orderitem.code like 'T___' and not conference_ticket.frozen group by assopy_order.country_id order by count desc; ```

…

-- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Experts (#1, May 10 2021)

>> Python Projects, Coaching and Support ... https://www.egenix.com/ >> Python Product Development ... https://consulting.egenix.com/

________________________________________________________________________ ::: We implement business ideas - efficiently in both time and costs ::: eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg Registered at Amtsgericht Duesseldorf: HRB 46611 https://www.egenix.com/company/contact/ https://www.malemburg.com/