# Moodle MySQL Queries

# Here are some Moodle MySQL Queries that are useful for generating activity statistics:

## From [http://blog.weber.k12.ut.us/jreeve/some-simple-mysql-queries-for-moodle/](http://blog.weber.k12.ut.us/jreeve/some-simple-mysql-queries-for-moodle/)

**Find the most popular activities:**

```
SELECT COUNT(l.id) hits, moduleFROM mdl_log lWHERE module != 'login' AND module != 'course' AND module != 'role'GROUP BY moduleORDER BY hits DESC
```

**Find the most active users over the past 7 days**   
(change the “604800″ to the number of the appropriate number of seconds if you want to adjust this interval):

```
SELECT COUNT(l.id) hits, l.userid, u.username, u.firstname, u.lastnameFROM mdl_log l INNER JOIN mdl_user u ON l.userid = u.idWHERE l.time > UNIX_TIMESTAMP(NOW()) - 604800GROUP BY l.useridORDER BY hits DESC
```

**Find the most active courses:**  
(You may need to change the second line to <span class="caps">FROM</span> mdl\_log l <span class="caps">INNER</span> <span class="caps">JOIN</span> mdl\_course c ON l.course = c.id <span class="caps">AND</span> c.id != ‘1′ to omit home page hits)

```
SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursenameFROM mdl_log l INNER JOIN mdl_course c ON l.course = c.idGROUP BY courseIdORDER BY hits DESC
```

## Some custom written ones:

**Find the number of resources per course:**

```
SELECT COUNT(l.id) count, l.course, c.fullname coursenameFROM mdl_resource l INNER JOIN mdl_course c on l.course = c.idGROUP BY courseORDER BY count DESC
```